[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:  3.6
 Severity:  normal                   |  Resolution:
 Keywords:  has-patch needs-testing  |
-------------------------------------+------------------------------

Comment (by robertv123):

 before


 {{{
 SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND ( (
                                         SELECT COUNT(1)
                                         FROM wp_term_relationships
                                         WHERE term_taxonomy_id IN (5,453)
                                         AND object_id = wp_posts.ID
                                 ) = 2 ) AND wp_posts.post_type = 'post'
 AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY
 wp_posts.post_date DESC LIMIT 0, 20
 }}}

 after I split it up into many queries that mysql can handle easier. My
 guess is mysql does not cache a sub queries result. This creates a
 temporary table (mysql could do this automatically but that's another
 story, sub queries are well known to be poor in mysql)


 {{{
 //create the temporary tables
 create temporary table $table ( object_id bigint(20) unsigned,
 term_taxonomy_id bigint(20) unsigned)
 create temporary table $table1 ( object_id bigint(20) unsigned, c int )

 // collect all the objects + terms together into temp table
 insert into $table select  object_id , term_taxonomy_id from
 wp_term_relationships where term_taxonomy_id IN ($terms)
 // insert the counts of how many objects that have all $terms
 insert into $table1 select object_id,count(*) from $table group by
 object_id
 // drop the first temp table that was use to collect the data
 DROP TABLE $table

 // add the second table to the where query
 $join .= " , $table1 AS tt ";
 $where = " tt.c = $num_terms AND tt.object_id = wp_posts.ID "
 }}
 This would make the finial query (I did this manually so hopefully I got
 it right)
 {{
 SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts , $table1 AS tt
 WHERE 1=1  AND (  tt.c = $num_terms AND tt.object_id = wp_posts.ID  ) AND
 wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP
 BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 20

 }}}

 The concerns I have is that the second temp table is currently not if
 persistent connections are used. This would cause problems over time if
 the same connect was used many times either due to high volume or the
 connection being open for a long term.

 So a hook is needed to drop the second temporary table after the sql query
 is completed

 OR

 a better query that does not require temporary tables

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