[wp-trac] [WordPress Trac] #19973: Query Maxing Out CPU
WordPress Trac
wp-trac at lists.automattic.com
Sun Feb 5 17:51:32 UTC 2012
#19973: Query Maxing Out CPU
--------------------------+-----------------------------
Reporter: ballhogjoni | Owner: ballhogjoni
Type: defect (bug) | Status: new
Priority: normal | Milestone: Awaiting Review
Component: Database | Version: 3.3.1
Severity: critical | Keywords:
--------------------------+-----------------------------
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?
--
Ticket URL: <http://core.trac.wordpress.org/ticket/19973>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software
More information about the wp-trac
mailing list