Optimize SQL resulting from custom taxonomy queries

[misterbisson]

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

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

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

@markjaquith: new patch attached to address your points.

Some performance tests are in order.

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.

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.

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.

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

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.