Make WordPress Core

Opened 13 years ago

Closed 13 years ago

#17488 closed defect (bug) (duplicate)

Extremely inefficient queries in archives for multiple tags (terms)

Reported by: nkuttler's profile nkuttler Owned by:
Milestone: Priority: normal
Severity: major Version: 3.1.2
Component: Query Keywords: needs-patch
Focuses: Cc:

Description

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.

Change History (1)

#1 @scribu
13 years ago

  • Milestone Awaiting Review deleted
  • Resolution set to duplicate
  • Status changed from new to closed

Please see #16706

Note: See TracTickets for help on using tickets.