[wp-trac] [WordPress Trac] #16706: Queries using "category__and" are slow on large databases
WordPress Trac
wp-trac at lists.automattic.com
Mon Feb 28 23:33:26 UTC 2011
#16706: Queries using "category__and" are slow on large databases
--------------------------+------------------------------
Reporter: tigertech | Owner:
Type: defect (bug) | Status: new
Priority: normal | Milestone: Awaiting Review
Component: Database | Version: 3.1
Severity: normal | Resolution:
Keywords: |
--------------------------+------------------------------
Comment (by tigertech):
By the way, I showed this explanation to a co-worker here who said it
wasn't clear why the second version is so much faster. So:
The reason it's faster is that it can operate almost entirely on the
wp_term_relationships index. For each row (post) in the outer query, the
inner query uses the PRIMARY index to load a very small number of rows
based on the object_id. So it ends up with something like five rows, which
it can then examine to see if they match "term_taxonomy_id IN (1,461)". (A
single index on "(object_id, term_taxonomy_id)" would probably speed this
up even more and eliminate the "Using where" on the subquery, operating
entirely on the index.)
In contrast, the "GROUP BY" version attacks it the other way round: it
first uses the term_taxonomy_id index to search for rows in
wp_term_relationships that match "term_taxonomy_id IN (1,461)".
Unfortunately, it ends up with tens of thousands in the worst case,
instead of a handful, which it then shoves into a temporary table. It then
sorts the unindexed temporary table to do the COUNT. That might be fine if
MySQL 5.0.x only did that once, but it does it once for each outer query
row, even though the results of the inner query are identical (constant)
each time through.
--
Ticket URL: <http://core.trac.wordpress.org/ticket/16706#comment:1>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software
More information about the wp-trac
mailing list