WordPress › Support User Favorites: mikeo75

WordPress › Support User Favorites: mikeo75 http://wordpress.org/support/ WordPress › Support User Favorites: mikeo75 en Tue, 24 Nov 2009 08:26:39 +0000 rwl4 on "MySQL Slow query problem" http://wordpress.org/support/topic/192707/page/2#post-922511 Fri, 12 Dec 2008 15:25:49 +0000 rwl4 922511@http://wordpress.org/support/ I am having this exact problem right now. Any help would be MUCH appreciated. <blockquote>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 ('3', '74', '11', '7', '8', '9', '10', '14', '4', '5', '80', '69', '15', '13', '12', '77', '78', '76', '75', '79', '43', '44', '42', '41', '91', '51', '52', '50', '49', '92', '55', '56', '54', '53', '93', '59', '60', '58', '57', '94', '63', '64', '62', '61', '95', '25', '28', '26', '24', '86', '18', '19', '17', '16', '96', '21', '23', '22', '20', '97', '83', '84', '82', '81', '85', '72', '73', '71', '70', '87', '36', '35', '34', '33', '89', '31', '32', '30', '29', '88', '39', '40', '38', '37', '90') AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') LIMIT 0, 4;</blockquote> We have been in the process of moving a site with 40k posts from custom software to Wordpress and this is really causing a problem. powderboy on "MySQL Slow query problem" http://wordpress.org/support/topic/192707/page/2#post-876902 Wed, 15 Oct 2008 17:31:04 +0000 powderboy 876902@http://wordpress.org/support/ What, exactly did you guys do to "fix" this? Was it just indexing the taxonomy table? I'm getting killed right now on <a href="http://www.bluehost.com." rel="nofollow">www.bluehost.com.</a> CPU exceeded errors and SQL queries in the 100+ CPU seconds range. beisbolct on "MySQL Slow query problem" http://wordpress.org/support/topic/192707#post-868433 Fri, 03 Oct 2008 15:34:33 +0000 beisbolct 868433@http://wordpress.org/support/ That's exactly the same problem i'm having again. Niteblade on "MySQL Slow query problem" http://wordpress.org/support/topic/192707#post-858826 Mon, 22 Sep 2008 02:09:41 +0000 Niteblade 858826@http://wordpress.org/support/ The number one query eating my sever alive is: <code>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;</code> Running Explain: <blockquote>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</blockquote> 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. beisbolct on "MySQL Slow query problem" http://wordpress.org/support/topic/192707#post-844758 Thu, 04 Sep 2008 02:34:33 +0000 beisbolct 844758@http://wordpress.org/support/ 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. Ogre on "MySQL Slow query problem" http://wordpress.org/support/topic/192707#post-843681 Tue, 02 Sep 2008 21:18:17 +0000 Ogre 843681@http://wordpress.org/support/ 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? beisbolct on "MySQL Slow query problem" http://wordpress.org/support/topic/192707#post-842838 Mon, 01 Sep 2008 21:57:44 +0000 beisbolct 842838@http://wordpress.org/support/ I fixed that problem, now i'm getting lock times from a different part of the database that queries the comments. Ogre on "MySQL Slow query problem" http://wordpress.org/support/topic/192707#post-842009 Sun, 31 Aug 2008 18:47:13 +0000 Ogre 842009@http://wordpress.org/support/ > 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 beisbolct on "MySQL Slow query problem" http://wordpress.org/support/topic/192707#post-841599 Sun, 31 Aug 2008 03:48:26 +0000 beisbolct 841599@http://wordpress.org/support/ 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 beisbolct on "MySQL Slow query problem" http://wordpress.org/support/topic/192707#post-840880 Fri, 29 Aug 2008 22:20:13 +0000 beisbolct 840880@http://wordpress.org/support/ 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 beisbolct on "MySQL Slow query problem" http://wordpress.org/support/topic/192707#post-839310 Thu, 28 Aug 2008 03:32:13 +0000 beisbolct 839310@http://wordpress.org/support/ # 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 on "MySQL Slow query problem" http://wordpress.org/support/topic/192707#post-838260 Tue, 26 Aug 2008 21:46:30 +0000 Ogre 838260@http://wordpress.org/support/ beisbolct, what are the queries that are causing problems? beisbolct on "MySQL Slow query problem" http://wordpress.org/support/topic/192707#post-835450 Sat, 23 Aug 2008 02:00:01 +0000 beisbolct 835450@http://wordpress.org/support/ 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. mikeo75 on "MySQL Slow query problem" http://wordpress.org/support/topic/192707#post-831308 Sun, 17 Aug 2008 19:30:00 +0000 mikeo75 831308@http://wordpress.org/support/ No idea, can't get an answer from anyone :( beisbolct on "MySQL Slow query problem" http://wordpress.org/support/topic/192707#post-830376 Sat, 16 Aug 2008 05:14:00 +0000 beisbolct 830376@http://wordpress.org/support/ So how do we fix the taxonomy.php file to work correctly? mikeo75 on "MySQL Slow query problem" http://wordpress.org/support/topic/192707#post-828846 Thu, 14 Aug 2008 05:57:16 +0000 mikeo75 828846@http://wordpress.org/support/ 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 mikeo75 on "MySQL Slow query problem" http://wordpress.org/support/topic/192707#post-828818 Thu, 14 Aug 2008 05:04:59 +0000 mikeo75 828818@http://wordpress.org/support/ The plugin appears to be Similar Posts, which sucks, because that's a pretty nice feature. Not sure how to make it better or not. Dalton, my server at Media Temple has this in their control panel, so I'm not sure your VPS will have it. daltonrooney on "MySQL Slow query problem" http://wordpress.org/support/topic/192707#post-827357 Tue, 12 Aug 2008 14:39:02 +0000 daltonrooney 827357@http://wordpress.org/support/ mikeo75: I am wondering if you can educate me a little bit on how you logged this query? I am interested in tuning the performance of my site and finding out where there are bottlenecks. In your original post, you wrote: Created 06:55 PM 07/28/2008 ### 74 Queries ### Total time: 154, Average time: 2.08108108108108 ### Taking 2 to 3 seconds to complete ### Rows analyzed 55576 - 56375 etc., etc. I have shell access and am about to move to a VPS. Will I be able to do logging like this myself? Thanks, Dalton beisbolct on "MySQL Slow query problem" http://wordpress.org/support/topic/192707#post-818448 Thu, 31 Jul 2008 19:43:15 +0000 beisbolct 818448@http://wordpress.org/support/ Mikeo I was wondering if it was that too since that plugin uses a plugin library to run. mikeo75 on "MySQL Slow query problem" http://wordpress.org/support/topic/192707#post-818046 Thu, 31 Jul 2008 12:57:58 +0000 mikeo75 818046@http://wordpress.org/support/ I'm wondering if it's similar posts? I'll try to find out. Ogre on "MySQL Slow query problem" http://wordpress.org/support/topic/192707#post-817404 Wed, 30 Jul 2008 19:06:40 +0000 Ogre 817404@http://wordpress.org/support/ I'm curious about which plugin is executing this query. The author needs to be educated a small amount on the dangers of querying on unindexed columns. It would be very easy for the author to add the indexing as part of the installation process. I'm also interested in the results of my suggestions. mikeo75 on "MySQL Slow query problem" http://wordpress.org/support/topic/192707#post-817172 Wed, 30 Jul 2008 13:34:49 +0000 mikeo75 817172@http://wordpress.org/support/ Good stuff Ogre, I appreciate the help...I'm going to try and run the EXPLAIN thing on this query and see what happens. Ogre on "MySQL Slow query problem" http://wordpress.org/support/topic/192707#post-816734 Wed, 30 Jul 2008 01:05:36 +0000 Ogre 816734@http://wordpress.org/support/ Oops, no quotes around the word in the parens. My bad. Ogre on "MySQL Slow query problem" http://wordpress.org/support/topic/192707#post-816731 Wed, 30 Jul 2008 01:00:10 +0000 Ogre 816731@http://wordpress.org/support/ To add an index via phpmyadmin, select the table you need to modify, find the the column name in the list, look on the far right for the 'action' that adds an index and click it. On my system with a very small table it's done almost instantly. If you have a lot of data in the table it might take a few seconds. From within a mysql shell, you can add the table with this command: ALTER TABLE wp_term_taxonomy ADD INDEX ( 'taxonomy' ); beisbolct on "MySQL Slow query problem" http://wordpress.org/support/topic/192707#post-816726 Wed, 30 Jul 2008 00:52:05 +0000 beisbolct 816726@http://wordpress.org/support/ how do you do the index... index wp_taxonomy.taxonomy i am having the same problem on mine as well. Ogre on "MySQL Slow query problem" http://wordpress.org/support/topic/192707#post-816678 Tue, 29 Jul 2008 23:45:50 +0000 Ogre 816678@http://wordpress.org/support/ Some explanation on how to optimize a query. Take the query giving you problems and run it with the word 'EXPLAIN' prepended, e.g. "EXPLAIN SELECT t.*,tt.* FROM ...". What you get is a table explaining how the query was handled. Look at the column called "key". If any of them have "null", that's bad. Also look at the column called "rows". A big number is bad. Several rows with big numbers are very bad. Multiply all the values in "rows" and that's the number of rows your query had to look at to come up with the result. A badly written query could have looked at millions of rows (it can actually be more rows than you have in your database because the same row could be examined multiple times!). The trick (and this is were a good DBA earns his big bucks) is to reduce the number of rows looked at without affecting the query. First off, get rid of the "null" in the "key" column by indexing the taxonomy column. After that, it's a matter of rewriting the query, which is a bit beyond my skillset (I'm just a linux system admin) for a query this complicated. Ogre on "MySQL Slow query problem" http://wordpress.org/support/topic/192707#post-816649 Tue, 29 Jul 2008 23:02:01 +0000 Ogre 816649@http://wordpress.org/support/ You should get a slight improvement if you index wp_taxonomy.taxonomy. If you have phpmyadmin, adding this index is simple. If you can get "slow query logging" enabled hand have access to the log, it should point out where you can make improvements. The taxonomy column was the query item I could spot that was not indexed properly. mikeo75 on "MySQL Slow query problem" http://wordpress.org/support/topic/192707#post-816442 Tue, 29 Jul 2008 19:40:47 +0000 mikeo75 816442@http://wordpress.org/support/ This is what I see for query caching: # query cache query_cache_limit = 1048576 query_cache_size = 12M query_cache_type = 1 Also, changing that query you said, it will just get wiped out in the next release I assume. Not even sure what file that is in :) Ogre on "MySQL Slow query problem" http://wordpress.org/support/topic/192707#post-816386 Tue, 29 Jul 2008 18:27:35 +0000 Ogre 816386@http://wordpress.org/support/ Ask your host to enable query caching. It can make a HUGE difference. Ogre on "MySQL Slow query problem" http://wordpress.org/support/topic/192707#post-816384 Tue, 29 Jul 2008 18:26:06 +0000 Ogre 816384@http://wordpress.org/support/ You must have a lot of different slugs and/or categories. That's the only reason I can see for the query to take so long. I ran the query on my server, but because of the limited amount of data it took no time to run: "Showing rows 0 - 3 (4 total, Query took 0.0002 sec)". You might change this: WHERE tt.taxonomy IN ( 'post_tag' ) To WHERE tt.taxonomy='post_tag' And see if you get any improvement.