[wp-hackers] SELECT SQL_CALC_FOUND_ROWS distinct wp_posts query

[Jeremi Bergman]

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