wordpress generating slow mysql queries - is it index problem?
- by tash
Hello Stack Overflow
I've got very slow Mysql queries coming up from my wordpress site. It's making everything slow and I think this is eating up CPU usage. I've pasted the Explain results for the two most frequently problematic queries below. This is a typical result - although very occasionally teh queries do seem to be performed at a more normal speed.
I have the usual wordpress indexes on the database tables. You will see that one of the queries is generated from wordpress core code, and not from anything specific - like the theme - for my site.
I have a vague feeling that the database is not always using the indexes/is not using them properly...
Is this right? Does anyone know how to fix it? Or is it a different problem entirely?
Many thanks in advance for any help anyone can offer - it is hugely appreciated
Query: [wp-blog-header.php(14): wp()]
SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') ORDER BY wp_posts.post_date DESC LIMIT 0, 6
id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
1
SIMPLE
wp_posts
ref
type_status_date
type_status_date
63
const
427
Using where; Using filesort
Query time: 34.2829 (ms)
9) Query: [wp-content/themes/LMHR/index.php(40): query_posts()]
SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.ID NOT IN ( SELECT tr.object_id FROM wp_term_relationships AS tr INNER JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy = 'category' AND tt.term_id IN ('217', '218', '223', '224') ) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') ORDER BY wp_posts.post_date DESC LIMIT 0, 6
id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
1
PRIMARY
wp_posts
ref
type_status_date
type_status_date
63
const
427
Using where; Using filesort
2
DEPENDENT SUBQUERY
tr
ref
PRIMARY,term_taxonomy_id
PRIMARY
8
func
1
Using index
2
DEPENDENT SUBQUERY
tt
eq_ref
PRIMARY,term_id_taxonomy,taxonomy
PRIMARY
8
antin1_lovemusic2010.tr.term_taxonomy_id
1
Using where
Query time: 70.3900 (ms)
