MySQL Slow query problem

Actually, I think it’s Taxonomy.php that is causing this problem.

Here is what my host found:

Unfortunately, I won’t be able to pin point where this query is coming from since wordpress uses variables but you can perform searches for certain key words that might at least lower the amount of scripts. I did a quick search that looks for one of the inner joins lines

INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id

You can run this from within ssh

egrep -ir “INNER JOIN .* AS tt ON t.term_id = tt.term_id” /home/43539/domains/

This looks to be retuning files with part of this query,

/home/43539/domains/popcritics.com/html/wp-includes/taxonomy.php
/home/43539/domains/popcritics.com/html/wp-content/plugins/simple-tags/2.3/simple-tags.client.php
/home/43539/domains/popcritics.com/html/wp-content/plugins/simple-tags/2.5/simple-tags.client.php
/home/43539/domains/popcritics.com/html/wp-content/plugins/simple-tags/2.5/inc/simple-tags.admin.php

So how do we fix the taxonomy.php file to work correctly?

No idea, can’t get an answer from anyone ??

It sucks too because all of mysql slow queries that’s causing wordpress to use too much cpu on my site is coming from it.

beisbolct, what are the queries that are causing problems?

# Wed Aug 27 08:07:02 2008
# Query_time: 7 Lock_time: 0 Rows_sent: 1 Rows_examined: 4
use ianbethu_wdold;
SELECT * , IF (DATE_ADD(link_updated, INTERVAL 120 MINUTE) >= NOW(), 1,0) as recently_updated FROM wp_links INNER JOIN wp_term_relationships AS tr ON (wp_links.link_id = tr.object_id) INNER JOIN wp_term_taxonomy as tt ON tt.term_taxonomy_id = tr.term_taxonomy_id WHERE 1=1 AND link_visible = ‘Y’ AND ( tt.term_id = 2 ) AND taxonomy = ‘link_category’ ORDER BY link_name ASC

# Wed Aug 27 08:26:42 2008
# Query_time: 19 Lock_time: 17 Rows_sent: 17 Rows_examined: 17
use ianbethu_wrdp1;
DESC wp_comments

# Wed Aug 27 08:26:42 2008
# Query_time: 19 Lock_time: 7 Rows_sent: 17 Rows_examined: 17
use ianbethu_wrdp1;
DESC wp_comments

# Wed Aug 27 08:36:41 2008
# Query_time: 24 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
use ianbethu_wrdp1;
UPDATE wp_options SET option_value = ‘1219847777’ WHERE option_name = ‘gltr_last_connection_time’

# Wed Aug 27 09:22:20 2008
# Query_time: 18 Lock_time: 0 Rows_sent: 16 Rows_examined: 64
use ianbethu_wrdp1;
SELECT * , IF (DATE_ADD(link_updated, INTERVAL 120 MINUTE) >= NOW(), 1,0) as recently_updated FROM wp_links INNER JOIN wp_term_relationships AS tr ON (wp_links.link_id = tr.object_id) INNER JOIN wp_term_taxonomy as tt ON tt.term_taxonomy_id = tr.term_taxonomy_id WHERE 1=1 AND link_visible = ‘Y’ AND ( tt.term_id = 88 ) AND taxonomy = ‘link_category’ ORDER BY link_name ASC

# Wed Aug 27 09:22:20 2008
# Query_time: 11 Lock_time: 0 Rows_sent: 5 Rows_examined: 25
use ianbethu_wrdp1;
SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN (‘category’, ‘post_tag’) AND tr.object_id IN (1844) ORDER BY t.name ASC

# Wed Aug 27 09:25:27 2008
# Query_time: 7 Lock_time: 0 Rows_sent: 4 Rows_examined: 20
use ianbethu_wrdp1;
SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN (‘category’, ‘post_tag’) AND tr.object_id IN (2629) ORDER BY t.name ASC

# Wed Aug 27 09:25:27 2008
# Query_time: 6 Lock_time: 0 Rows_sent: 1 Rows_examined: 4
use ianbethu_wdold;
SELECT * , IF (DATE_ADD(link_updated, INTERVAL 120 MINUTE) >= NOW(), 1,0) as recently_updated FROM wp_links INNER JOIN wp_term_relationships AS tr ON (wp_links.link_id = tr.object_id) INNER JOIN wp_term_taxonomy as tt ON tt.term_taxonomy_id = tr.term_taxonomy_id WHERE 1=1 AND link_visible = ‘Y’ AND ( tt.term_id = 2 ) AND taxonomy = ‘link_category’ ORDER BY link_name ASC

# Wed Aug 27 09:25:27 2008
# Query_time: 6 Lock_time: 0 Rows_sent: 1 Rows_examined: 4
use ianbethu_wrdp1;
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 (‘858’) AND wp_posts.post_type = ‘post’ AND (wp_posts.post_status = ‘publish’) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 15

ogre here are some more from today that have an absurd query time.

# Fri Aug 29 16:03:45 2008
# Query_time: 10 Lock_time: 6 Rows_sent: 1 Rows_examined: 0
use ianbethu_wrdp1;
SELECT wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.ID = 2259 AND wp_posts.post_type = ‘post’ ORDER BY wp_posts.post_date DESC

# Fri Aug 29 16:03:47 2008
# Query_time: 6 Lock_time: 0 Rows_sent: 12 Rows_examined: 24
use ianbethu_wrdp1;
SELECT * FROM wp_comments WHERE comment_post_ID = 3687 AND comment_approved = ‘1’ ORDER BY comment_date

# Fri Aug 29 16:03:53 2008
# Query_time: 4 Lock_time: 0 Rows_sent: 1 Rows_examined: 2882
use ianbethu_wrdp1;
SELECT p.* FROM wp_posts AS p WHERE p.post_date < ‘2007-09-17 20:00:35’ AND p.post_type = ‘post’ AND p.post_status = ‘publish’ ORDER BY p.post_date DESC LIMIT 1

# Fri Aug 29 16:13:02 2008
# Query_time: 35 Lock_time: 0 Rows_sent: 3 Rows_examined: 15
use ianbethu_wrdp1;
SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN (‘category’, ‘post_tag’) AND tr.object_id IN (3208) ORDER BY t.name ASC

# Fri Aug 29 16:13:02 2008
# Query_time: 4 Lock_time: 0 Rows_sent: 2 Rows_examined: 10
SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN (‘category’, ‘post_tag’) AND tr.object_id IN (3039) ORDER BY t.name ASC

# Fri Aug 29 16:13:27 2008
# Query_time: 16 Lock_time: 0 Rows_sent: 1 Rows_examined: 1325
use ianbethu_wrdp1;
SELECT p.* FROM wp_posts AS p WHERE p.post_date < ‘2008-04-05 17:18:33’ AND p.post_type = ‘post’ AND p.post_status = ‘publish’ ORDER BY p.post_date DESC LIMIT 1

# Fri Aug 29 16:13:42 2008
# Query_time: 39 Lock_time: 0 Rows_sent: 1 Rows_examined: 1968
use ianbethu_wrdp1;
SELECT p.* FROM wp_posts AS p WHERE p.post_date < ‘2008-01-21 15:19:41’ AND p.post_type = ‘post’ AND p.post_status = ‘publish’ ORDER BY p.post_date DESC LIMIT 1

# Fri Aug 29 16:13:42 2008
# Query_time: 39 Lock_time: 0 Rows_sent: 1 Rows_examined: 2137
SELECT p.* FROM wp_posts AS p WHERE p.post_date < ‘2007-12-28 15:00:43’ AND p.post_type = ‘post’ AND p.post_status = ‘publish’ ORDER BY p.post_date DESC LIMIT 1

# Fri Aug 29 16:13:42 2008
# Query_time: 29 Lock_time: 0 Rows_sent: 1 Rows_examined: 4047
use ianbethu_wrdp1;
SELECT p.* FROM wp_posts AS p WHERE p.post_date < ‘2007-04-14 18:26:11’ AND p.post_type = ‘post’ AND p.post_status = ‘publish’ ORDER BY p.post_date DESC LIMIT 1

# Fri Aug 29 16:13:43 2008
# Query_time: 45 Lock_time: 0 Rows_sent: 15 Rows_examined: 1164
use ianbethu_wrdp1;
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 (’29’) AND wp_posts.post_type = ‘post’ AND (wp_posts.post_status = ‘publish’) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 15

Here’s more and I don’t even use a K2 theme at all yet it’s calling for a K2 in some type of query.

# Sat Aug 30 21:24:50 2008
# Query_time: 121 Lock_time: 78 Rows_sent: 1 Rows_examined: 1
use ianbethu_wrdp1;
SELECT option_value FROM wp_options WHERE option_name = ‘k2installed’ LIMIT 1

# Sat Aug 30 21:24:52 2008
# Query_time: 119 Lock_time: 74 Rows_sent: 1 Rows_examined: 1
use ianbethu_wrdp1;
SELECT option_value FROM wp_options WHERE option_name = ‘k2installed’ LIMIT 1

# Sat Aug 30 21:24:52 2008
# Query_time: 127 Lock_time: 81 Rows_sent: 1 Rows_examined: 1
use ianbethu_wrdp1;
SELECT option_value FROM wp_options WHERE option_name = ‘k2installed’ LIMIT 1

# Sat Aug 30 21:24:52 2008
# Query_time: 117 Lock_time: 72 Rows_sent: 1 Rows_examined: 1
use ianbethu_wrdp1;
SELECT option_value FROM wp_options WHERE option_name = ‘k2installed’ LIMIT 1

# Sat Aug 30 21:24:52 2008
# Query_time: 122 Lock_time: 77 Rows_sent: 1 Rows_examined: 1
SELECT option_value FROM wp_options WHERE option_name = ‘k2installed’ LIMIT 1

# Sat Aug 30 21:24:52 2008
# Query_time: 115 Lock_time: 70 Rows_sent: 1 Rows_examined: 1
use ianbethu_wrdp1;
SELECT option_value FROM wp_options WHERE option_name = ‘k2installed’ LIMIT 1

# Sat Aug 30 21:24:52 2008
# Query_time: 120 Lock_time: 75 Rows_sent: 1 Rows_examined: 0
use ianbethu_wrdp1;
SELECT wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.ID = 3836 AND wp_posts.post_type = ‘post’ ORDER BY wp_posts.post_date DESC

# Sat Aug 30 21:24:53 2008
# Query_time: 115 Lock_time: 72 Rows_sent: 1 Rows_examined: 1
use ianbethu_wrdp1;
SELECT option_value FROM wp_options WHERE option_name = ‘k2installed’ LIMIT 1

# Sat Aug 30 21:24:53 2008
# Query_time: 113 Lock_time: 71 Rows_sent: 1 Rows_examined: 1
SELECT option_value FROM wp_options WHERE option_name = ‘k2installed’ LIMIT 1

# Sat Aug 30 21:24:53 2008
# Query_time: 112 Lock_time: 70 Rows_sent: 1 Rows_examined: 1
use ianbethu_wrdp1;
SELECT option_value FROM wp_options WHERE option_name = ‘k2installed’ LIMIT 1

# Sat Aug 30 21:24:53 2008
# Query_time: 107 Lock_time: 65 Rows_sent: 1 Rows_examined: 1
use ianbethu_wrdp1;
SELECT option_value FROM wp_options WHERE option_name = ‘k2installed’ LIMIT 1

# Sat Aug 30 21:24:53 2008
# Query_time: 108 Lock_time: 66 Rows_sent: 1 Rows_examined: 1
use ianbethu_wrdp1;
SELECT option_value FROM wp_options WHERE option_name = ‘k2installed’ LIMIT 1

# Sat Aug 30 21:24:53 2008
# Query_time: 103 Lock_time: 61 Rows_sent: 1 Rows_examined: 1
use ianbethu_wrdp1;
SELECT option_value FROM wp_options WHERE option_name = ‘k2installed’ LIMIT 1

# Sat Aug 30 21:24:53 2008
# Query_time: 116 Lock_time: 70 Rows_sent: 1 Rows_examined: 1
use ianbethu_wrdp1;
SELECT option_value FROM wp_options WHERE option_name = ‘k2installed’ LIMIT 1

# Sat Aug 30 21:24:57 2008
# Query_time: 102 Lock_time: 91 Rows_sent: 1 Rows_examined: 1
use ianbethu_wrdp1;
SELECT option_value FROM wp_options WHERE option_name = ‘k2installed’ LIMIT 1

# Sat Aug 30 21:24:58 2008
# Query_time: 97 Lock_time: 87 Rows_sent: 1 Rows_examined: 1
use ianbethu_wrdp1;
SELECT option_value FROM wp_options WHERE option_name = ‘k2installed’ LIMIT 1

# Sat Aug 30 21:24:58 2008
# Query_time: 97 Lock_time: 87 Rows_sent: 1 Rows_examined: 1
use ianbethu_wrdp1;
SELECT option_value FROM wp_options WHERE option_name = ‘k2installed’ LIMIT 1

# Sat Aug 30 21:24:58 2008
# Query_time: 90 Lock_time: 81 Rows_sent: 1 Rows_examined: 1
SELECT option_value FROM wp_options WHERE option_name = ‘k2installed’ LIMIT 1

# Sat Aug 30 21:24:58 2008
# Query_time: 84 Lock_time: 75 Rows_sent: 1 Rows_examined: 1
use ianbethu_wrdp1;
SELECT option_value FROM wp_options WHERE option_name = ‘k2installed’ LIMIT 1

# Sat Aug 30 21:24:58 2008
# Query_time: 82 Lock_time: 73 Rows_sent: 1 Rows_examined: 1
use ianbethu_wrdp1;
SELECT option_value FROM wp_options WHERE option_name = ‘k2installed’ LIMIT 1

# Sat Aug 30 21:24:58 2008
# Query_time: 81 Lock_time: 72 Rows_sent: 1 Rows_examined: 1
use ianbethu_wrdp1;
SELECT option_value FROM wp_options WHERE option_name = ‘k2installed’ LIMIT 1

# Sat Aug 30 21:24:58 2008
# Query_time: 67 Lock_time: 59 Rows_sent: 1 Rows_examined: 1
use ianbethu_wrdp1;
SELECT option_value FROM wp_options WHERE option_name = ‘k2installed’ LIMIT 1

# Sat Aug 30 21:24:58 2008
# Query_time: 72 Lock_time: 64 Rows_sent: 1 Rows_examined: 1
use ianbethu_wrdp1;
SELECT option_value FROM wp_options WHERE option_name = ‘k2installed’ LIMIT 1

# Sat Aug 30 21:24:58 2008
# Query_time: 41 Lock_time: 36 Rows_sent: 1 Rows_examined: 1
use ianbethu_wrdp1;
SELECT option_value FROM wp_options WHERE option_name = ‘k2deliciousname’ LIMIT 1

> Query_time: 121 Lock_time: 78 Rows_sent: 1 Rows_examined: 1

Is that in seconds? There is no reason a query with so few rows involved to take that long. There is something wrong with the mysql server that your host needs to address.

Ok, there’s one possibility that may not be their fault. If something keeps locking tables or the entire database, it will block the query. I see there are some pretty high lock times. That’s a very bad thing. Remove whatever is doing the locks

I fixed that problem, now i’m getting lock times from a different part of the database that queries the comments.

There is never an reason to lock tables on a simple select query. Something must be doing inserts or updates. Perhaps a logging module that is recording how often a query is viewed?

I’ve moved to a new host and no problems so far. It could have been my previous hosts mysql. I have no idea. I don’t know mysql that well other than basic things.

The number one query eating my sever alive is:

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 ('11') AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 1;

Running Explain:

id = 1
select_type = simple
type = const
possible_keys = PRIMARY,term_id_taxonomy,term_taxonomy_id
key = term_id_taxonomy
key_len = 106
ref = const,const
rows = 1
extra = Using index; Using temporary; Using filesort

id = 1
select_type = simple
type = index
possible_keys = primary
key = primary
key_len = 16
ref = null
rows = 117780
extra = Using where; Using index

id = 1
select_type = simple
type = ref
possible_keys = primary
key = primary
key_len = 132
ref = shop_shopus.wp_term_relationships.object_id,const,…
rows = 1
extra = Using where

Massive amounts CPU power is used, and the dedicated server becomes sluggish for a few minutes until this query has passed … like a kidney stone.

That’s exactly the same problem i’m having again.

What, exactly did you guys do to “fix” this? Was it just indexing the taxonomy table?

I’m getting killed right now on https://www.bluehost.com. CPU exceeded errors and SQL queries in the 100+ CPU seconds range.