Use subqueries for taxonomy queries

[ryan]

Fetching posts within a given categories/tags would be faster with subqueries. 2.9 will bump the MySQL requirement to 4.1 so we can rewrite to make use of subqueries.

[ryan]

Sample query:

SELECT $wpdb->posts.ID FROM  $wpdb->posts WHERE
$wpdb->posts.ID IN (
     SELECT $wpdb->term_relationships.object_id FROM $wpdb->term_relationships
     WHERE $wpdb->term_relationships.term_taxonomy_id IN (
          SELECT $wpdb->term_taxonomy.term_taxonomy_id FROM $wpdb->term_taxonomy
          WHERE $wpdb->term_taxonomy.term_id = '$cat_id' AND $wpdb->term_taxonomy.taxonomy = 'category'
          )
     )
AND $wpdb->posts.post_type = 'post'
AND $wpdb->posts.post_status = 'draft' "
ORDER BY $wpdb->posts.post_date DESC LIMIT $limit OFFSET $offset

[Denis-de-Bernardy]

actually, that particular one would be much faster with a join:

SELECT $wpdb->posts.ID FROM  $wpdb->posts
JOIN $wpdb->term_relationships
ON $wpdb->term_relationships.object_id = $wpdb->posts.ID
JOIN $wpdb->term_taxonomy
ON $wpdb->term_taxonomy.term_taxonomy_id = $wpdb->term_relationships.term_taxonomy_id
AND $wpdb->term_taxonomy.term_id = '$cat_id'
AND $wpdb->term_taxonomy.taxonomy = 'category'
WHERE $wpdb->posts.post_type = 'post'
AND $wpdb->posts.post_status = 'draft' "
ORDER BY $wpdb->posts.post_date DESC LIMIT $limit OFFSET $offset

else, you end up with a temporary table, a merge join plan, and no index whatsoever if the mysql optimizer misses the potential optimization.

the same kind of query holds for tags. for a combo of one tag and one category, using a big join will be fastest.

the issue is when we query for multiple terms. one with multiple tags and categories would become something like the following and it no longer uses any kind of meaningful index, because it's a tough one to for optimizers to get right:

SELECT $wpdb->posts.ID FROM  $wpdb->posts
JOIN $wpdb->term_relationships as cat_rel
ON cat_rel.object_id = $wpdb->posts.ID
JOIN $wpdb->term_taxonomy as cat_tax
ON cat_tax.term_taxonomy_id = cat_rel.term_taxonomy_id
AND cat_tax.term_id IN ( '$cat_ids' )  AND cat_rel.taxonomy = 'category'
JOIN $wpdb->term_relationships as tag_rel
ON tag_rel.object_id = $wpdb->posts.ID
JOIN $wpdb->term_taxonomy as tag_tax
ON tag_tax.term_taxonomy_id = tag_rel.term_taxonomy_id
AND tag_tax.term_id IN ( '$tag_ids' )  AND tag_tax.taxonomy = 'tag'
WHERE $wpdb->posts.post_type = 'post'
AND $wpdb->posts.post_status = 'draft' "
GROUP BY $wpdb->posts.ID
ORDER BY $wpdb->posts.post_date DESC LIMIT $limit OFFSET $offset

where:

$cat_ids = join("', '", array_map('intval', $cat_ids));
$tag_ids = join("', '", array_map('intval', $tag_ids));

the latter is a case where, intuitively, a subquery along the lines of your example would probably win based on the table stats because we then get rid of the group by and end up with a hash join plan. but it'll probably be the exception.

[Denis-de-Bernardy]

err, nm on the merge join mention in the first query, it's definitely looking forward for a hash join. but anyway... :-P

using the joins instead of subqueries will be faster if there is no IN() clause, and an order by limit.

[dd32]

What about multiple-taxonomy querying Denis?

Or is that as easy as?

AND (
tag_tax.term_id IN ( '$tag_ids' )  AND tag_tax.taxonomy = 'tag'
OR 
tag_tax.term_id IN ( '$cat_ids' )  AND tag_tax.taxonomy = 'cat'
)

[ryan]

I haven't profiled any of this myself, but that query I snippeted is something that one of the VIPs on wordpress.com used in place of the default query and supposedly got much, much better performance.

[nacin]

#12891