id	summary	reporter	owner	description	type	status	priority	milestone	component	version	severity	resolution	keywords	cc
19973	Query Maxing Out CPU	ballhogjoni	ballhogjoni	"I have a blog with over 18K posts and 116K records in post meta table. I noticed an inefficient query that slows down the site. I only have mysql running on this server and its at a constant 700-800% CPU load. Im running wp 3.3.1.

The load avg is at 15% all the time. I don't know where its at and can't seem to fix it. I disabled all the plugins and the query still runs.

Here is  the query that is being ran:
{{{
SELECT count(*) as c FROM wp_posts WHERE ID IN (SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id = 18562) AND ID IN (SELECT post_id FROM wp_postmeta WHERE meta_key = '_menu_item_menu_item_parent' AND meta_value = '56824') ORDER BY menu_order
}}}

When I run an explain on the query I get this:

{{{
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	wp_posts	ALL	NULL	NULL	NULL	NULL	18241	Using where
3	DEPENDENT SUBQUERY	wp_postmeta	index_subquery	post_id,meta_key	post_id	8	func	6	Using where
2	DEPENDENT SUBQUERY	wp_term_relationships	unique_subquery	PRIMARY,term_taxonomy_id	PRIMARY	16	func,const	1	Using index; Using where
}}}

So it runs a complete table scan of the posts table on every page load. How do I avoid/fix this?"	defect (bug)	closed	normal		Database	3.3.1	major	invalid		ballhogjoni@…