WordPress.org

Make WordPress Core

Opened 5 years ago

Closed 4 years ago

#14699 closed enhancement (duplicate)

Optimize SQL resulting from custom taxonomy queries

Reported by: misterbisson Owned by:
Milestone: Priority: normal
Severity: normal Version: 3.0.1
Component: Query Keywords: has-patch needs-testing
Focuses: Cc:

Description

The SQL generated by WP_Query::get_posts() results in a get_objects_in_term() and implode()s the given post IDs into the posts query. The query can't be optimized at the database layer, and leads to slow performance with large numbers of posts. browse code

Attachments (1)

better-tax-sql.diff (1.2 KB) - added by misterbisson 5 years ago.
aliases the term_taxonomy and term_relationship tables to avoid conflicting with other joins

Download all attachments as: .zip

Change History (9)

comment:1 @markjaquith5 years ago

I think you need to use unique table references, or these will conflict with tag/tax queries, for one.

@misterbisson5 years ago

aliases the term_taxonomy and term_relationship tables to avoid conflicting with other joins

comment:2 @misterbisson5 years ago

@markjaquith: new patch attached to address your points.

comment:3 @scribu5 years ago

  • Keywords has-patch needs-testing added; query mysql optimization removed
  • Type changed from defect (bug) to enhancement

Some performance tests are in order.

comment:4 @misterbisson5 years ago

A note on performance: this could be made faster yet if the terms were resolved to term_taxonomy_ids and only the the term_relationships table was joined. The difficulty there is with get_term_children() which expects and returns only term_ids.

Adding this patch, however, is a good stepping stone to further optimizations.

comment:5 follow-up: @markjaquith5 years ago

My first thought on this issue was to replace the PHP-created IN() list with a subquery. Often times two simple queries are faster than one complicated one.

comment:6 in reply to: ↑ 5 @misterbisson5 years ago

Replying to markjaquith:

The subquery is indeed faster. I ran some tests against a table set with about 15,000 terms, 181,000 term relationships, and 19,000 posts (running on a default MySQL config on a MacBook Pro). Here are the results:

SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS wp_posts.* 
FROM wp_posts 
INNER JOIN wp_term_relationships tax_tr ON (wp_posts.ID = tax_tr.object_id) 
INNER JOIN wp_term_taxonomy tax_tt ON (tax_tr.term_taxonomy_id = tax_tt.term_taxonomy_id) 
WHERE 1=1 
AND tax_tt.taxonomy = 'channel' 
AND tax_tt.term_id IN ('334540') 
AND wp_posts.post_type = 'post' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_date DESC 
LIMIT 0, 10;

This repeatedly returned results in about .4 seconds

SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS wp_posts.* 
FROM wp_posts 
WHERE 1=1 
AND wp_posts.ID IN ( 
	SELECT SQL_NO_CACHE tax_tr.object_id
	FROM wp_term_relationships tax_tr
	INNER JOIN wp_term_taxonomy tax_tt ON (tax_tr.term_taxonomy_id = tax_tt.term_taxonomy_id) 
	WHERE tax_tt.taxonomy = 'channel' 
	AND tax_tt.term_id IN ('334540') 
)
AND wp_posts.post_type = 'post' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_date DESC 
LIMIT 0, 10;

This repeatedly returned results in about .08 seconds

The subquery approach is likely also better suited to doing intersections.

comment:7 @scribu5 years ago

  • Milestone changed from Awaiting Review to 3.1

Nice. I guess the only reason we didn't use subqueries before is that they weren't supported by MySQL then.

comment:8 @scribu4 years ago

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

I'm going to mark this as duplicate of #12891 since the SQL code has been moved out of WP_Query.

Please post suggestions for improvements there.

Note: See TracTickets for help on using tickets.