wp query – Optimizing some built-in WordPress queries on a large website

Question

My website’s db is quite big. I have some 350,000 images in the media library (as well as some 350,000 posts). My wp_postmeta table has 4,580,552 rows.

As a result, some queries are insanely slow and I would like to somehow alter them. Starting with this query: Searching in the media library.

A search generates the following query:

SELECT wp_posts.ID
FROM wp_posts
LEFT JOIN wp_postmeta AS sq1
ON ( wp_posts.ID = sq1.post_id
AND sq1.meta_key = '_wp_attached_file' )
WHERE 1=1
AND (((wp_posts.post_title LIKE '%London%')
OR (wp_posts.post_excerpt LIKE '%London%')
OR (wp_posts.post_content LIKE '%London%')
OR (sq1.meta_value LIKE '%London%')))
AND wp_posts.post_type="attachment"
AND ((wp_posts.post_status="inherit"
OR wp_posts.post_status="private"))
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 20

Takes approximately 14 seconds. I’d like to disable the inclusion of searching in the fille name (which is redundent for us) – so that the query will not need wp_postmeta and look like this:

SELECT wp_posts.ID
FROM wp_posts
WHERE 1=1
AND (((wp_posts.post_title LIKE '%London%')
OR (wp_posts.post_excerpt LIKE '%London%')
OR (wp_posts.post_content LIKE '%London%')))
AND wp_posts.post_type="attachment"
AND ((wp_posts.post_status="inherit"
OR wp_posts.post_status="private"))
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 20

(This query, without wp_postmeta, takes 0.03 seconds).

Any advice how I can disable search of the attachment file names would be greatly appreciated.

0
Biranit Goren 4 months 2023-11-11T09:32:12-05:00 0 Answers 0 views 0

Leave an answer

Browse
Browse