Slow querys when the DB is too big

I have used the plugin to develop a directory with custom permalinks.
The directory has about 100,000 records.

And when you try to access a page, it increases the CPU and RAM consumption of the server.

The server itself even blocks the connection for security.

Currently the plugin in some pages generates 1 or 2 slow queries for each page view.

For example:

SELECT p.ID, pm.meta_value, p.post_type, p.post_status
FROM wp_posts AS p
INNER JOIN wp_postmeta AS pm
ON (pm.post_id = p.ID)
WHERE pm.meta_key = 'custom_permalink'
AND (pm.meta_value = ''
OR pm.meta_value = '/')
AND p.post_status != 'trash'
AND p.post_type != 'nav_menu_item'
ORDER BY FIELD(post_status,'publish','private','pending','draft','auto-draft','inherit'), FIELD(post_type,'post','page')
LIMIT 1

and

SELECT p.ID, pm.meta_value, p.post_type, p.post_status
FROM wp_posts AS p
LEFT JOIN wp_postmeta AS pm
ON (p.ID = pm.post_id)
WHERE meta_key = 'custom_permalink'
AND meta_value != ''
AND ( LOWER(meta_value) = LEFT(LOWER(''), LENGTH(meta_value))
OR LOWER(meta_value) = LEFT(LOWER('/'), LENGTH(meta_value)) )
AND post_status != 'trash'
AND post_type != 'nav_menu_item'
ORDER BY LENGTH(meta_value) DESC, FIELD(post_status,'publish','private','pending','draft','auto-draft','inherit'), FIELD(post_type,'post','page'), p.ID ASC
LIMIT 1

The time of these queries is 23s and 22s. Therefore the web becomes inaccessible.

Is there any way to solve this problem?

• This topic was modified 2 years, 7 months ago by seliyu. Reason: gramtic