querying optimization for category AND searching
|Reported by:||robertv123||Owned by:|
Description (last modified by johnbillion)
querying category with an AND for two term_id's plus having a 50,000 + posts site causes the query produced by get_sql in wp-includes/taxonomy.php to take excessive amount of time.
On my site the current query in trunk takes 200 + seconds and the optimizes query takes 100 to 200 milliseconds.
Sub selects in mysql are not optimized on the server very well.
Example code to utilize query
query_posts(array('category__and' => array($term0_id,$term1_id)));
affected code starts on line 724 of wp-includes/taxonomy.php
- I simply added the temp table to the $join variable not knowing if this will cause problems on other queries
- Clean up of the temporary tables may be needed after query is executed with a persistent connection but in my case they can be left there as mysql will garbage clean them after script disconnects.
Attached is a patch to wp-includes/taxonomy.php