[wp-trac] [WordPress Trac] #24837: querying optimization for category AND searching

[WordPress Trac]

#24837: querying optimization for category AND searching
-------------------------------------+------------------------------
 Reporter:  robertv123               |       Owner:
     Type:  enhancement              |      Status:  new
 Priority:  normal                   |   Milestone:  Awaiting Review
Component:  Taxonomy                 |     Version:  trunk
 Severity:  normal                   |  Resolution:
 Keywords:  has-patch needs-testing  |
-------------------------------------+------------------------------
Description changed by johnbillion:

Old description:

> 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
>
> Potential problems.
>
> 1. I simply added the temp table to the $join variable not knowing if
> this will cause problems on other queries
> 2. 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

New description:

 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

 Potential problems.

 1. I simply added the temp table to the $join variable not knowing if this
 will cause problems on other queries
 2. 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

--

--
Ticket URL: <http://core.trac.wordpress.org/ticket/24837#comment:1>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software