ag211I run a relatively large scale website of over 11,000 posts and over 100,000 comments and have been having problems as the site is growing and traffic is spiking.
This site has been customized quite a bit through our own theme and I suspect this could be the problem
This query is constantly apearing in my slow query list
# Query_time: 6 Lock_time: 0 Rows_sent: 4 Rows_examined: 45008
SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) WHERE 1=1 AND wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN ('3', '96', '97') AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 4;
another example...
# Query_time: 10 Lock_time: 0 Rows_sent: 16 Rows_examined: 45002
SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) WHERE 1=1 AND wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN ('3', '96', '97') AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 16;
I can figure out what's triggering it and how to optimize or even just cache it. I takes 3 or even 6 seconds to return the results. You can imagine what happens when over 14,000 unique visitors visit the site daily.
I would appreciate if anyone could help me with this problem.
