Improving MySQL Select statement in WordPress theme

Question

I'm using the WordPress theme Engine from Industrialthemes and see that the rendering of the front page uses a lot of queries that uses around 0.4 seconds to run in my MySQL database. Like this one:

SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE 1 =1
  AND (wp_term_relationships.term_taxonomy_id IN (1))
  AND wp_posts.post_type = 'post'
  AND (wp_posts.post_status = 'publish'
       OR wp_posts.post_status = 'closed')
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC 
LIMIT 0,  5;

Is there any way this query can be improved? As far as I can see, the WordPress installation have the default indexes in place for all involved fields. My knowledge on tuning SQL Select statements is not good so I'm hoping for some experts to help me on this one. Thanks.

(From Comment)

CREATE TABLE wp_term_relationships (
    object_id bigint(20) unsigned NOT NULL DEFAULT '0', 
    term_taxonomy_id bigint(20) unsigned NOT NULL DEFAULT '0', 
    term_order int(11) NOT NULL DEFAULT '0', 
    PRIMARY KEY (object_id,term_taxonomy_id), 
    KEY term_taxonomy_id (term_taxonomy_id), 
    KEY idx1 (object_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

(Later...)

CREATE TABLE wp_term_relationships (
    object_id bigint(20) unsigned NOT NULL DEFAULT '0', 
    term_taxonomy_id bigint(20) unsigned NOT NULL DEFAULT '0', 
    term_order int(11) NOT NULL DEFAULT '0', 
    PRIMARY KEY (object_id,term_taxonomy_id), 
    KEY term_taxonomy_id (term_taxonomy_id), 
    KEY idx1 (term_taxonomy_id,object_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Answer 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?