[wp-trac] [WordPress Trac] #19973: Query Maxing Out CPU

[WordPress Trac]

#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