I’m optimizing high traffic website. Trying to solve following issue:
Site’s home page is extremely slow and top showing 100% CPU because of Mysql. After some investigation with Query Monitor plugin, I’ve found slow query, but can’t optimize it. I have 400 posts with ACF. I need to load last 15 posts sorted by date and some ACF fields and also show total posts.
So, the problem is by default, wordpress preload all meta of queried posts. I’ve tried 2 cases:
Load all posts without meta_query and collect only posts with condition I need. In this case WordPress preload all meta of this posts with query
SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE
post_id IN (…) ORDER BY meta_id ASC`
In my case it’s 65k records and of course it’s slow query.
Load only posts with my conditions in meta_query. It’s query like
SELECT wp_posts.* FROM wp_posts LEFT JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN
ON ( wp_posts.ID = wp_postmeta.post_id ) LEFT JOIN wp_postmeta AS
ON ( wp_posts.ID = mt1.post_id ) LEFT JOIN wp_postmeta AS mt2
ON (wp_posts.ID = mt2.post_id AND mt2.meta_key = ‘***’ ) WHERE 1=1
AND ( wp_term_relationships.term_taxonomy_id IN (6) ) AND (
p_postmeta.meta_key = ‘***’ AND ( ( mt1.meta_key = ‘***’ AND
mt1.meta_value != ‘1’ ) OR mt2.post_id IS NULL ) ) AND
wp_posts.post_type IN (‘post’, ‘page’, ‘attachment’, ‘pre_sale’)
AND ((wp_posts.post_status = ‘publish’)) GROUP BY wp_posts.ID
ORDER BY CAST(wp_postmeta.meta_value AS DATETIME) DESC`
This query is very slow too (in my case about 10sec).
How to optimize it? Website is under WP Super Cache, but registered users (editors) viewing not cached pages. As temporally solution, I limit query to 30 rows and show total with found_posts, but this data is incorrect.
Read more here:: Query posts without meta preload