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 wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) LEFT JOIN wp_postmeta AS mt1 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.