Help

This time it’s a query. I’ve been emailing back and forth with my host about it for two days. I thought the problem was a database table from a plugin I’m not using anymore. It’s not a table for a plugin that’s causing the problem. I realized today it’s a WordPress database table (wp_post2cats). I’ve optimized it, but I’m guessing if I drop the table something really bad will happen.

This is the snippet of code my host sent me tonight.

# User@Host: terrance[terrance] @ [209.68.2.83]
# Query_time: 11 Lock_time: 0 Rows_sent: 94 Rows_examined: 772405
SELECT cat_ID, cat_name, category_nicename,category_parent,
COUNT(wp_post2cat.post_id) AS cat_count,
DAYOFMONTH(MAX(post_date)) AS lastday,
MONTH(MAX(post_date)) AS lastmonth
FROM wp_categories LEFT JOIN wp_post2cat ON (cat_ID =
category_id)
LEFT JOIN wp_posts ON (ID = post_id)
WHERE cat_ID > 0
GROUP BY cat_ID HAVING cat_count > 0 ORDER BY cat_name
asc, post_date DESC;

According to my host, this one took 11 seconds to run. There have been instances when it takes 90 seconds. When it takes too long, my host locks my database for 2 hours in order to let it reset. This has happened three times in the last two days. Evidently, the problem is 700,000 rows checked and 94 sent.

I’ve been emailing back and forth with them. They can’t tell me what’s causing the problem or how to fix it. All they can tell me is that this is the query I need to work on. I have no idea what to do with it or how to fix it. I’ve tried optimizing the database and the table itself, but that doesn’t seem to have solved the problem. I’ve posted to the WordPress support forum, but I have no idea if or when I’l hear anything on that front.

My host is on me to fix the problem. I have no idea how. If there isn’t a solution soon, I’m not sure whether I’m going to have to just take the blog and two years worth of content down and start all over again from nothing.

If there’s anybody who can help me, or has any idea what needs to be done, I’d really appreciate it. I’ve reached the end of my meager knowledge of databases, coding and command lines. I’m quickly approaching the end of my options.

About Terrance

Black. Gay. Father. Buddhist. Vegetarian. Liberal.
This entry was posted in Wordpress Tech Stuff. Bookmark the permalink.

3 Responses to Help

  1. I think this is something a lot of us are suddenly having issues with.  I’ve had database lockdowns on three separate wp installations this week for no reason, and your post is the first I’ve even seen a pinpoint on the problem.

    Over on Broken Kode, Mint was the problem, or at least appears to be the problem.
    My suggestion would be to turn off your plugins one at a time, because even though it may be a core wordpress database file, it could be that a plugin is grinding the database. I’ve also discovered that Akismet (if you’re using it) can be a culprit as well, along with Technorati crawls/pings. If you find the answer, post something. In the meantime I’ll hunt around, too, because I’m sure I’m going to hear from my host shortly on the same issues.

  2. Waveflux says:

    If you haven’t already, you should visit the WordPress support board with issue. Someone there may have had the same problem; at any rate, they’ll be more familiar than your host with the plug.

  3. I just saw that WordPress has a security update available for download and installation which addresses several recent problems.  You might want to check out their post here:
    http://wordpress.org/development/2006/03/security-202/ and see if updating your version f wordpress solves the issues with database/server grinds.

Comments are closed.