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] @ [126.96.36.199]
# 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 =
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.