Query Maxing Out CPU

[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?

[scribu]

Well, one idea would be to avoid the wp_posts table altogether; just join postmeta with wp_term_relationships. The ORDER BY menu_order is completely useless.

[ballhogjoni]

Thanks for the reply. How would I go about doing that...this is a query that wordpress creates.

[scribu]

You'll have to dig through the backtrace to see where it comes from.

[ballhogjoni]

There isn't some sort of quick fix? I can't be the only one having this issue.

[scribu]

It would be nice to have a quick fix for everything, wouldn't it. Reality doesn't work that way, though.

[ocean90]

There could be a "quick fix" if we know where it comes from, see scribu's comment.

Did you have configured a custom nav menu?

[ballhogjoni]

Hey ocean90 that's part of the problem I don't know how to get a backtrace to locate where its being called. My guess is the loop.

Yes there is a nav menu term.

[scribu]

The ​Debug Bar plugin should help you locate the function that is generating the query.

[ballhogjoni]

Thanks scribu...I installed that plugin and refresh my site...I saw a lot of queries but not the one this ticket is about. So I am completely lost. When I run 'show full processlist' in mysql from the terminal I see the query this ticket is about. So I don't know what is going on. What can I provide for you to help me further.

Thanks,
Chris

[scribu]

Maybe that query is only run on certain pages (or possibly in the admin area).

[ballhogjoni]

Ok I tried a few other pages...like category and tag pages, plus I tried the dashboard of the admin. I didn't see it in the debug plugin.

That term_taxonomy_id in the query points to the nav_menu taxonomy in the wp_term_taxonomy table, this record has a term_id of 18492. When I view record 18492 in the wp_terms table I see the term's name = Top Menu and slug = top-menu

Don't know if that helps, but I appreciate you working with me.

This is the output when I run show full processlist.

mysql> SHOW FULL PROCESSLIST;
+----------+--------------+-------------------------------------------+----------------------+---------+------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id       | User         | Host                                      | db                   | Command | Time | State          | Info                                                                                                                                                                                                                                                                |
+----------+--------------+-------------------------------------------+----------------------+---------+------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 17270255 | leechprotect | localhost                                 | leechprotect         | Sleep   | 4013 |                | NULL                                                                                                                                                                                                                                                                |
| 17286335 | root         | localhost                                 | NULL                 | Query   |    0 | NULL           | SHOW FULL PROCESSLIST                                                                                                                                                                                                                                               |
| 17286349 | username     | static.cloud-ips.com:35551 | dbname | Query   |    0 | preparing      | 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 = '56783') ORDER BY menu_order |
| 17286351 | username     | static.cloud-ips.com:35552 | dbname | Sleep   |    2 |                | NULL   

[SergeyBiryukov]

This query looks like the one from WPtouch Pro plugin, see wptouch_wordpress_menu_has_children() function in wptouch-pro/include/template-tags/wordpress-menu.php:

$sql = $wpdb->prepare( "SELECT count(*) as c FROM {$wpdb->base_prefix}posts WHERE ID IN (SELECT object_id FROM {$wpdb->base_prefix}term_relationships WHERE term_taxonomy_id = %d) AND ID IN (SELECT post_id FROM {$wpdb->base_prefix}postmeta WHERE meta_key = '_menu_item_menu_item_parent' AND meta_value = '%d') ORDER BY menu_order", $settings->custom_menu_name, $parent );

It's a premium plugin, not available in WP.org directory.

[scribu]

That would certainly explain it.

[ballhogjoni]

awesome thanks for the help!