wordpress generating slow mysql queries - is it index problem?

Question

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)

Answer 1

You can try wp-cache plugin. Also you can read this article it explains that SQL_CALC_FOUND_ROWS not the best solution at queries.

Answer 2

same problem here with w3 total cache and varnish, i have a blog with more than 15k posts, when i publish a page (note: not a post, a page!) the database go down executing these queries... any solutions?

Answer 3

Same problem here, mysql makes server load skyrocket to 190+ .. server is unresponsive.

Seriously, I know this is an old post but if there is any wordpress/sql veteran who can help me with this I would be very grateful. I can even donate or pay something in return.

Any input appreciated!