[wp-hackers] SELECT SQL_CALC_FOUND_ROWS distinct wp_posts query

[Otto]

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
>