[wp-hackers] SELECT SQL_CALC_FOUND_ROWS distinct wp_posts query

Fri Oct 16 21:20:16 UTC 2009

> Sounds like that plugin needs an update.
>
> The category__not_in parameter works just fine for query_posts, WP_Query, etc.

I will agree with Otto, but Jeremi you may need to consider overall load

This is the SQL you gave:

SELECT SQL_CALC_FOUND_ROWS distinct wp_posts.* FROM wp_posts LEFT JOIN
wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy ON wp_term_taxonomy.term_taxonomy_id =
wp_term_relationships.term_taxonomy_id WHERE 1=1 AND wp_posts.post_type =
'post' AND (wp_posts.post_status = 'publish') AND NOT EXISTS (SELECT * FROM
wp_term_relationships JOIN wp_term_taxonomy ON
wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
WHERE wp_term_relationships.object_id = wp_posts.ID AND
wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN (89)
) ORDER BY wp_posts.post_date DESC LIMIT 0, 50;

but the following are the SQL generated by
query_posts(array('category__not_in' => array(86), 'posts_per_page' =>
50));

SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  WHERE 1=1  AND
wp_posts.ID NOT IN ( SELECT tr.object_id FROM wp_term_relationships AS
tr INNER JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id =
tt.term_taxonomy_id WHERE tt.taxonomy = 'category' AND tt.term_id IN
('86') ) AND wp_posts.post_type = 'post' AND (wp_posts.post_status =
'publish' OR wp_posts.post_status = 'private')  ORDER BY
wp_posts.post_date DESC LIMIT 0, 50

SELECT FOUND_ROWS()

SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN
wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN
wp_term_relationships AS tr ON tr.term_taxonomy_id =
tt.term_taxonomy_id WHERE tt.taxonomy IN ('category', 'post_tag') AND
tr.object_id IN (<fifty post ids>) ORDER BY t.name ASC

SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN
(<fifty post ids>)

whereas I suspect you only need the first one.

Good luck!

W

2009/10/16 Otto <otto at ottodestruct.com>:
> Sounds like that plugin needs an update.
>
> The category__not_in parameter works just fine for query_posts, WP_Query, etc.
>
>
> -Otto
> Sent from Memphis, TN, United States
>
>
> On Fri, Oct 16, 2009 at 1:56 PM, Jeremi Bergman <jeremib at gmail.com> wrote:
>> Actually, I believe you are right.  I narrowed it down to the *Advanced
>> Category Excluder*  plugin.  I've disabled it, and am watching the slow
>> query logs.
>>
>> Thanks
>>
>> On Fri, Oct 16, 2009 at 1:35 PM, Otto <otto at ottodestruct.com> wrote:
>>
>>> That doesn't appear to be a core query. At least, not in the latest
>>> trunk (no idea on 2.8.4). The word "EXISTS" is not used in that sort
>>> of context anywhere in the code that I can see.
>>>
>>> Got any weird plugins?
>>>
>>> -Otto
>>> Sent from Memphis, TN, United States
>>>
>>>
>>> On Fri, Oct 16, 2009 at 11:44 AM, Jeremi Bergman <jeremib at gmail.com>
>>> wrote:
>>> > It seems there's this one query,
>>> > SELECT SQL_CALC_FOUND_ROWS distinct wp_posts.* FROM wp_posts LEFT JOIN
>>> > wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
>>> > LEFT JOIN wp_term_taxonomy ON wp_term_taxonomy.term_taxonomy_id =
>>> > wp_term_relationships.term_taxonomy_id WHERE 1=1 AND wp_posts.post_type =
>>> > 'post' AND (wp_posts.post_status = 'publish') AND NOT EXISTS (SELECT *
>>> FROM
>>> > wp_term_relationships JOIN wp_term_taxonomy ON
>>> > wp_term_taxonomy.term_taxonomy_id =
>>> wp_term_relationships.term_taxonomy_id
>>> > WHERE wp_term_relationships.object_id = wp_posts.ID AND
>>> > wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN
>>> (89)
>>> > ) ORDER BY wp_posts.post_date DESC LIMIT 0, 50;
>>> >
>>> > That is locking up my database and bringing down my site, at least once a
>>> > day. When I look in the slow queries log, it's full of them.
>>> >
>>> > # Time: 091016 8:56:50
>>> > # User at Host: mrsec_wp[mrsec_wp] @ localhost []
>>> > # Query_time: 13 Lock_time: 0 Rows_sent: 50 Rows_examined: 185675
>>> > use mrsec_wp;
>>> > SELECT SQL_CALC_FOUND_ROWS distinct wp_posts.* FROM wp_posts LEFT JOIN
>>> > wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
>>> > LEFT JOIN wp_term_taxonomy ON wp_term_taxonomy.term_taxonomy_id =
>>> > wp_term_relationships.term_taxonomy_id WHERE 1=1 AND wp_posts.post_type =
>>> > 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status =
>>> > 'private') AND NOT EXISTS (SELECT * FROM wp_term_relationships JOIN
>>> > wp_term_taxonomy ON wp_term_taxonomy.term_taxonomy_id =
>>> > wp_term_relationships.term_taxonomy_id WHERE
>>> wp_term_relationships.object_id
>>> > = wp_posts.ID AND wp_term_taxonomy.taxonomy = 'category' AND
>>> > wp_term_taxonomy.term_id IN (89) ) ORDER BY wp_posts.post_date DESC LIMIT
>>> 0,
>>> > 50;
>>> >
>>> > Any thoughts on how I can optimize this query?  It takes approx 13
>>> seconds
>>> > to execute this query. I have 28k records.
>>> >
>>> > Thanks
>>> > --
>>> > Jeremi Bergman
>>> > 865-622-7134
>>> > _______________________________________________
>>> > wp-hackers mailing list
>>> > wp-hackers at lists.automattic.com
>>> > http://lists.automattic.com/mailman/listinfo/wp-hackers
>>> >
>>> _______________________________________________
>>> wp-hackers mailing list
>>> wp-hackers at lists.automattic.com
>>> http://lists.automattic.com/mailman/listinfo/wp-hackers
>>>
>>
>>
>>
>> --
>> Jeremi Bergman
>> 865-622-7134
>> _______________________________________________
>> wp-hackers mailing list
>> wp-hackers at lists.automattic.com
>> http://lists.automattic.com/mailman/listinfo/wp-hackers
>>
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>