[wp-hackers] SELECT SQL_CALC_FOUND_ROWS distinct wp_posts query

[Otto]

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
>