Forum Replies Created

Viewing 8 replies - 1 through 8 (of 8 total)
  • @xtraxtra great news!
    i’m not a wordpress guru, but i think it’s one of the plugins that is calling this particular query.

    i am running 5.1.41 with 40k+ rows in wp-posts
    it’s just to do with the way the db server is optimising the query
    it doesn’t have anything to do with plugins?!
    it’s the actual wordpress code in taxonomy.php

    the code is new in version WP3.1
    it’s just an inefficient way of doing the subquery

    the new subquery works faster on 5.0 as well… i’ll submit this to trac when i work out how to use it.. ??

    this query:

    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;

    when run on the 5.0 server would take around 3-5seconds
    when run on thr 5.1 server it would take 3 minutes

    on the same data

    ubuntu 10.04 xen server
    4 cpus, 6gb ram

    it chugs on that query without any load on the server at all

    the problem is in taxonomy.php (wordpress 3.1.1)

    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)

    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. YMMV
    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


    FYI, on mySQL 5.0 the old query would take around 3-5seconds compared with 180sec on mySQL 5.1. YMMV

    OLD QUERY

    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 (code edit above gives this query)

    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;

    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;

Viewing 8 replies - 1 through 8 (of 8 total)