1

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:

  1. 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.

  1. 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.

  • You can stop WordPress from loading all meta data with update_post_meta_cache flag in WP_Query args. Really, meta data was never designed to be queried on, so any query that uses meta data in post selection is doomed to be slow. Taxonomies don't have this problem, which is why you should try to use taxonomies instead of meta data whenever possible. – Milo Mar 8 '18 at 4:20
  • Thank you @Milo. Good point. Unfortunatelly, ACF based on post_meta and it's a bad. – user3013494 Mar 8 '18 at 4:22
  • Is it an option to load the posts via ajax? – kero Mar 8 '18 at 8:05
  • @kero don't understand how to load total with ajax. In my case I need first 15 elements sorted by date and all elements. It's can't be solved via ajax – user3013494 Mar 8 '18 at 9:07
  • i think using transients api or another caching mechanism may work better. let the query run very slow. store the html output for repeated use. query only needs to run once an hour not every page load on home. depending on the data might not be reasonable but usually it is – Joel M Mar 8 '18 at 18:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.