[wp-trac] [WordPress Trac] #17288: slow query in taxonomy.php

[WordPress Trac]

#17288: slow query in taxonomy.php
-------------------------+-----------------------------
 Reporter:  fudj         |      Owner:
     Type:  enhancement  |     Status:  new
 Priority:  normal       |  Milestone:  Awaiting Review
Component:  Taxonomy     |    Version:  3.1
 Severity:  normal       |   Keywords:  needs-testing
-------------------------+-----------------------------
 platform: ubuntu 10.04
 wordpress: 3.1.1
 mysql: 5.1.43

 mySQL 5.1.43 (which is what I am running) optimises the query incorrectly,
 which is particularly slow on my site that has 2000+ posts (40000+ rows in
 wp_posts)

 OLD QUERY (took 180sec+ to execute)
 {{{
 SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  WHERE 1=1  AND (
 wp_posts.ID IN (
                                         SELECT object_id
                                         FROM wp_term_relationships
                                         WHERE term_taxonomy_id IN
 (4594,4600)
                                         GROUP BY object_id HAVING
 COUNT(object_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, 10;
 }}}

 NEW QUERY (<1sec)
 {{{
 SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  WHERE 1=1  AND
 EXISTS (
                        SELECT 1
                        FROM wp_term_relationships
                        WHERE term_taxonomy_id IN (4594,4600)
     AND object_id = wp_posts.ID
                        GROUP BY object_id HAVING COUNT(object_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, 10;
 }}}

 the way to get around it is to change the sub query in taxonomy.php:

     LINE 697-702 - replace with:
 {{{
     $where[] = "EXISTS (
         SELECT 1
         FROM $wpdb->term_relationships
         WHERE term_taxonomy_id IN ($terms)
         AND object_id = $primary_table.$primary_id_column
         GROUP BY object_id HAVING COUNT(object_id) = $num_terms
     )";
 }}}

     it forces the DB to evaluate inside first.
     I'd be interested if this works on other people's servers.
     There is a faster version using an inner join, but it changes the
 query structure so requires a bit more code editing that I haven't got
 around to looking at:


 POSSIBLE NEWER BETTER QUERY (requiring more code editing)
 {{{
     SELECT SQL_CALC_FOUND_ROWS  wp_posts.*
     FROM wp_posts
             INNER JOIN (
                     SELECT object_id
             FROM wp_term_relationships
             WHERE term_taxonomy_id IN (4594,4600)
             GROUP BY object_id
                     HAVING COUNT(object_id) = 2
             ) as term ON term.object_id = wp_posts.ID
     WHERE 1=1
             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, 10;
 }}}
 This one gets rid of the looping that occurs in the previous queries

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/17288>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software