[wp-hackers] SELECT SQL_CALC_FOUND_ROWS distinct wp_posts query

[Jeremi Bergman]

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