The section fronts on a Wordpress site I help out with have just started loading slow, and I traced it to the SQL query that loads the posts for the page, and timed a few different queries to see what's causing the slow-down.

SELECT wp_posts.ID
FROM wp_posts 
LEFT JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE ( wp_term_relationships.term_taxonomy_id IN (4,12,13,14) )
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 12

Takes 8.3 seconds to run.

SELECT wp_posts.ID
FROM wp_posts 
LEFT JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE ( wp_term_relationships.term_taxonomy_id IN (4,12,13,14) )
ORDER BY wp_posts.post_date DESC
LIMIT 12

Takes 6.5 seconds.

SELECT wp_posts.ID
FROM wp_posts 
LEFT JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE ( wp_term_relationships.term_taxonomy_id IN (4,12,13,14) )
LIMIT 12

Takes 31ms.

The SQL call is coming from Wordpress core so I don't really have control over that, but is there anything that I can do with the database or tell the company that maintains the database to do/check?

CodePudding user response：

It's worth using EXPLAIN to see how MySQL is using indexes (or not), and how it's optimizing the sorting (or not). See https://dev.mysql.com/doc/refman/8.0/en/using-explain.html

There might be an index that can help improve performance transparently, i.e. without requiring any change to the query in the Wordpress source code. That's not always possible, though.

There are other debugging techniques. I show some in a presentation: Sql query patterns, optimized

When asking questions about query optimization on Stack Overflow, you need to provide three things before you can get an answer:

• The query (which you have done).
• The SHOW CREATE TABLE result for each of the tables in the query, so we can see the data types, and current indexes and constraints.
• The output of EXPLAIN for the query (or queries) given your current table.

Sometimes it's helpful to use db-fiddle.com to create a minimal example that reproduces the test case, for folks to test with. I know you can't fill that with enough data to make it take 8 seconds, but at least enough to produce the same EXPLAIN result.

CodePudding user response：

• See Bill's answer
• You seem to have a zillion posts. It takes time to check each for the desired taxonomy.
• Then the query lists them whether or not they match. That is, LEFT is probably not what you wanted.
• GROUP BY is possibly unneeded. (Or the LEFT JOIN can be turned into an EXISTS.)
• INDEX(term_taxonomy_id, wp_term_relationships) on table wp_term_relationships may help.

This may be a better way to phrase the query (in addition to the added index):

SELECT  p.ID
    FROM ( SELECT DISTINCT tr.object_id
             FROM wp_term_relationships AS tr
             WHERE tr.term_taxonomy_id IN (4,12,13,14)
         ) AS ids
    JOIN wp_posts AS p  ON p.id = ids.object_id
    ORDER BY p.post_date DESC
    LIMIT 12

If you are not actually displaying just the IDs, it would be better to proceed with a JOIN to get more info for actually displaying. But I guess WP thwarts that optimization.