Improving MySQL Select statement in WordPress theme

1 comment

1. wp_term_relationships needs INDEX(term_taxonomy_id, object_id) -- in this order
   wp_posts might benefit from INDEX(post_type, ID, post_status, post_date) -- in this order
   

   Both are “covering” indexes.
   The former lets the JOIN work efficiently and gives the optimizer the option of starting with wp_term_relationships. And it should replace KEY term_taxonomy_id (term_taxonomy_id).
   The latter should work well regardless of which table is picked first.

   (More)

   SELECT  SQL_NO_CACHE SQL_CALC_FOUND_ROWS p.ID
       FROM  wp_posts AS p
        WHERE p.post_type = 'post'
         AND  p.post_status IN ( 'publish', 'closed' )
         AND EXISTS ( SELECT 1 FROM wp_term_relationships AS tr
                        WHERE p.ID = tr.object_id
                          AND tr.term_taxonomy_id IN (1) )
       ORDER BY  p.post_date DESC
       LIMIT  0, 5;
   

   With this formulation,

   If the EXPLAIN starts with p:

   p:  (post_date, post_type, post_status, ID)
   p:  (post_type, post_status, ID, post_date)
   tr:  (object_id, term_taxonomy_id)  -- which you have
   

   If the EXPLAIN starts with tr:

   p:  (ID)  -- which you probably have
   tr:  (term_taxonomy_id, object_id)
   

   The main problems:

   • The GROUP BY was adding effort. (I eliminated it by changing the JOIN to an EXISTS.)
   • IN ( 'publish', 'closed' ) — inhibits effective use of index.
   • SQL_CALC_FOUND_ROWS — means that it can’t stop when it gets 5 rows.
   • IN (1) turn into = 1, which is fine; but IN (1,2) is messier.

   Or, to be more blunt, WP has not yet been engineered to scale.

   Please add the indexes and get the EXPLAIN SELECT.

   From pastebin:

   SELECT  SQL_NO_CACHE p.ID
       FROM  wp_posts AS p
       WHERE  p.post_type = 'post'
         AND  p.post_status = 'publish'
         AND  EXISTS 
       (
           SELECT  1
               FROM  wp_term_relationships AS tr
               WHERE  p.ID = tr.object_id
                 AND  EXISTS 
               (
                   SELECT  1
                       from  wp_term_taxonomy AS tt
                       WHERE  tr.term_taxonomy_id = tt.term_taxonomy_id
                         AND  tt.taxonomy = 'post_tag'
                         AND  tt.term_id IN (548, 669) ) 
       );
   

   This is a different query. It needs this also:

   tt:  INDEX(term_taxonomy_id, taxonomy,  -- in either order
              term_id)   -- last
   

   And…

   SELECT  SQL_NO_CACHE wp_posts.ID
       FROM  wp_posts
       INNER JOIN  wp_term_relationships tr 
              ON (wp_posts.ID = tr.object_id)
       INNER JOIN  wp_term_taxonomy tt
              ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
       WHERE  ( post_type = 'post'
                 AND  post_status = 'publish'
                 AND  tt.taxonomy = 'post_tag'
                 AND  tt.term_id IN (548, 669)  
              )
       GROUP BY  wp_posts.ID;
   

   needs

   tt:  INDEX(taxonomy, term_id, term_taxonomy_id)  -- in this order
   

   I would add both of those indexes to tt and see what happens to the EXPLAINs and to performance.

   Rewrite query See if this gives you the ‘right’ answer:

   SELECT  p.ID, p.post_name, p.post_title,
           p.post_type, p.post_status,
           tt.term_id as termid, tt.taxonomy
       FROM  wp_posts AS p
       INNER JOIN  wp_term_relationships tr  ON (p.ID = tr.object_id)
       INNER JOIN  wp_term_taxonomy tt  ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
       WHERE  p.post_type = 'post'
         AND  p.post_status = 'publish'
         AND  tt.taxonomy = 'post_tag'
         AND  tt.term_id IN (548, 669)
       ORDER BY  p.ID;
   

   Notes:

   • GROUP BY removed
   • AND/OR probably not working as expected: a AND b OR c is equivalent to (a AND b) OR c, but I think you wanted a AND (b OR c)
   • Did you add the recommended indexes?

Comments are closed.