I have got very slow Mysql queries approaching from the wordpress site. It's making everything slow and i believe this really is eating up CPU usage. I have copied and pasted the Explain recent results for two of the most frequently problematic queries below. This can be a typical result - although very from time to time teh queries do appear to become carried out in a more normal speed.

I've the typical wordpress indexes around the database tables. You will notice that certain from the queries is produced from wordpress core code, and never from anything specific - such as the theme - for my website.

I've got a vague feeling the database isn't necessarily while using indexes/isn't with them correctly...

Is right? Does anybody understand how to repair it? Or perhaps is it another problem entirely?

Thank you ahead of time for just about any help anybody can provide - it's greatly appreciated

Query: [wordpress-blog-header.php(14): wordpress()]

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: [wordpress-content/styles/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)

You can test wp-cache wordpress plugin. You can also look at this article it describes that SQL_CALC_FOUND_ROWS not the very best solution at queries.

Take a look http://core.trac.wordpress.org/ticket/10964

The issue is SQL_CALC_NUM_ROWS, WordPress set this param instantly whenever you execute get_posts query, this create a slow query.