Question 2762 in Wordpress Question posted in
The official Wordpress documentation can be found here.

Debugging a slow WordPress SQL query (because of ORDER BY?)

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?

Tags:

3

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0
    • 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.

    Login or Signup to reply.
  3. 0

    There doesn’t seem to be an index appropriate to this particular query. It looks to me like you could use an extra one to help optimize that ORDER BY p.post_date DESC LIMIT 12 stuff.

    Try adding an index on wp_posts containing these columns: (ID, post_date DESC).

    To do that you can use wp-cli:

    wp db query "ALTER TABLE wp_posts ADD KEY goodspeed_id_date (ID, post_date DESC);"

    Or, if you do it via phpmyadmin use these two SQL statements.

    SET @@sql_mode := REPLACE(@@sql_mode, 'NO_ZERO_DATE', '');
ALTER TABLE wp_posts ADD KEY goodspeed_id_date (ID, post_date DESC);

    Please, please, please, let us know whether you are successful with this.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search