Where to start to optimize slow WP SQL query?-CodePudding

I have the following query, which currently takes about 0.3s to load, causing a heavy load on my Wordpress site.

SELECT SQL_CALC_FOUND_ROWS wp11_posts.ID
FROM wp11_posts
WHERE 1=1
AND ( wp11_posts.ID NOT IN (
SELECT object_id
FROM wp11_term_relationships
WHERE term_taxonomy_id IN (137,141) )
AND (
SELECT COUNT(1)
FROM wp11_term_relationships
WHERE term_taxonomy_id IN (53)
AND object_id = wp11_posts.ID ) = 1 )
AND wp11_posts.post_type = 'post'
AND ((wp11_posts.post_status = 'publish'))
GROUP BY wp11_posts.ID
ORDER BY wp11_posts.post_date DESC
LIMIT 0, 5

Where should I start to make it execute faster? Is there an apparent mistake standing out, that should definitely had been done differently?

CodePudding user response：

SQL_CALC_FOUND_ROWS requires doing nearly as much work as the same query without the LIMIT. [However, removing it without doing most of the following things probably won't help much.]
Do you already have this plugin installed? https://wordpress.org/plugins/index-wp-mysql-for-speed/ If not, that may be a good starting point.
WP is not designed to handle millions of posts/attributes/terms; you may have move on beyond WP.
Using JOIN or LEFT JOIN or [NOT] EXISTS ( SELECT 1 ... ) may be more efficient than IN ( SELECT ... ), especially in older versions of MySQL.

Is your SELECT COUNT(1) attempting to demand exactly 1? That is, 2 would be disallowed? If you really wanted to know if any exist, then use

AND EXISTS( SELECT 1 FROM wp11_term_relationships
              WHERE  term_taxonomy_id IN (53)
                AND  object_id = wp11_posts.ID )`

A better index for wp11_posts [I don't know whether your WP or the Plugin has this already]:

  INDEX(post_status, post_type,  -- first, either order is OK
        post_date, ID)           -- last, in this order

Having the GROUP BY and ORDER BY the 'same' may eliminate a sort. The following change will probably give you the same results, but faster.
```
  GROUP BY  wp11_posts.ID
  ORDER BY  wp11_posts.post_date DESC
```

-->

    GROUP BY  wp11_posts.post_date,      wp11_posts.ID
    ORDER BY  wp11_posts.post_date DESC, wp11_posts.ID DESC

CodePudding user response：

You have a so-called dependent subquery (a/k/a correlated subquery) in your example. It's a performance killer.

        WHERE (
            SELECT COUNT(1)
            FROM wp_term_relationships
            WHERE term_taxonomy_id IN (53)
                AND object_id = wp_posts.ID
              ) = 1

Refactoring it to an independent subquery looks like this:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts 
JOIN (
   SELECT object_id
     FROM wp_term_relationships
    WHERE term_taxonomy_id IN (53)
    GROUP BY object_id
    HAVING COUNT(*) = 1
     ) justone  ON wp_posts.ID = justone.object_id
... WHERE ...

See how this works? It needs to scan term_relationships just one time looking for object_ids meeting your criterion (just one). Then the ordinary inner JOIN excludes posts rows that don't meet that criterion. (The dependent subquery loops to scan the table multiple times, while we wait.)

The SQL_FOUND_ROWS thing: WordPress puts it there to help with "pagination" -- it lets WordPress figure out how many pages (in your case of five items) there are to display. It provides data to the familiar

 987 Items <<  <   2 of [ 20 ]  >  >>

page-selection interface you see in many parts of WordPress: it counts all the items matched by your query (987 in this example), not just one pageload of them.

If you don't need that pagination you can turn it off by giving a 'nopagination' => true element to WP_Query(). But if your query only yields a small number of items without the LIMIT clause, this probably doesn't matter much. If you wrote the query yourself, just leave it out along with the ORDER BY and LIMIT clauses.

So, leaving in the pagination stuff, a better query is

ANALYZE FORMAT=JSON  SELECT wp_posts.ID
FROM wp_posts
JOIN (
   SELECT object_id
     FROM wp_term_relationships
    WHERE term_taxonomy_id IN (53)
    GROUP BY object_id
    HAVING COUNT(*) = 1
     ) justone  ON wp_posts.ID = justone.object_id
WHERE 1 = 1
    AND (
        wp_posts.ID NOT IN (
            SELECT object_id
            FROM wp_term_relationships
            WHERE term_taxonomy_id IN (137,141)
            )
    AND wp_posts.post_type = 'post'
    AND wp_posts.post_status = 'publish')
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC LIMIT 0, 5

You also have an unnecessary GROUP BY near the end of your query. It doesn't hurt performance: MySQL can tell it's not needed in this case and doesn't do anything with it. But it is extra stuff. If you wrote the query yourself leave it out.