Query Causing MySQL to use all resources / crashes server

[user65]

Hello, I have the following posts for information and resources:
​http://wordpress.org/support/topic/database-maxing-cpu-usage-after-upgrade-to-31
​http://wordpress.org/support/topic/mysql-slow-queries-wordpress-31

My issue is that my host is telling me to move the wp-posts table to a different server, or different database... or SOMETHING. I dunno.

There is a query that is eating up all of my resources, This only happened since upgrading to 3.1. This query is available in the post I started - Database Maxing CPU Usage after Upgrade to 3.1.

I was thinking about using the modified query.php, but the ticket is a mess! http://core.trac.wordpress.org/ticket/10964

Here is the equivalent query from WP 3.0:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)  WHERE 1=1  AND wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN ('34') AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10

Could you run it and see if it's substantially faster?

First, thanks very much Scribu.

Man I'm a newbie when it comes to this stuff, I just do php and minor sql stuff... Like setting up, restoring, and exporting db's and such. I am very bad when it comes to running queries.

Now that is over with... I need to run this from mysql? or should I replace this in the query.php?

No, don't edit query.php.

Easiest would be to run it using phpMyAdmin. There's a little "Profiling" checkbox that you should check.

Of course, you can also run it through the command line, if you have shell access.

Thought so, thanks.

I ran it from the Command Shell in Webmin.

This is what I got back:

> SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)  WHERE 1=1  AND wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN ('34') AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10
bash: -c: line 0: syntax error near unexpected token `('
bash: -c: line 0: `SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)  WHERE 1=1  AND wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN ('34') AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10'

Erm, if you're running it from the shell, you need to log into mysql first, using something like this:

mysql -uYourMySQLUserName -pYourPassWord -D YourDataBaseName

If you need more help, I'm in the #wordpress-dev IRC channel on freenode.

After some back-and-forth with the reporter, we determined that it's not the query itself that's causing the spike.

Probably some faulty plugin or combination.