Extremely inefficient queries in archives for multiple tags (terms)

[nkuttler]

A subquery in FROM is executed once, a subquery in WHERE is executed for each row. This could be leveraged to massively increase the query efficiency for archives with multiple tags. At the moment archives for multiple tags are pretty useless once you have more than a few hundred posts.

Example:

SELECT SQL_CALC_FOUND_ROWS wp_public_posts.* FROM wp_public_posts 
WHERE 1=1 AND 
( wp_public_posts.ID IN ( 
  SELECT object_id FROM wp_public_term_relationships 
  WHERE term_taxonomy_id IN (170,127) 
  GROUP BY object_id 
  HAVING COUNT(object_id) = 2
) ) 
AND wp_public_posts.post_type = 'post' 
AND (wp_public_posts.post_status = 'publish' OR wp_public_posts.post_status = 'private') 
GROUP BY wp_public_posts.ID ORDER BY wp_public_posts.post_date DESC LIMIT 0, 10;

--> 2 rows in set (13.92 sec)

By simply doing the WHERE SELECT query once in advance we can use this query instead:

SELECT SQL_CALC_FOUND_ROWS wp_public_posts.* FROM wp_public_posts 
WHERE 1=1 AND ( wp_public_posts.ID IN ( 
  3614, 3740
 ) ) 
AND wp_public_posts.post_type = 'post' 
AND (wp_public_posts.post_status = 'publish' OR wp_public_posts.post_status = 'private') 
GROUP BY wp_public_posts.ID ORDER BY wp_public_posts.post_date DESC LIMIT 0, 10;

--> 2 rows in set (0.00 sec)

So I just decreased the page load time by almost 14 seconds. Yes, the query cache was reset for this.

[scribu]

Please see #16706