Query Maxing Out CPU
|Reported by:||ballhogjoni||Owned by:||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?
Change History (15)
comment:14 @scribu — 3 years ago
- Keywords close removed
- Milestone Awaiting Review deleted
- Resolution set to invalid
- Status changed from new to closed