Problems with Slow Queries

  • TerranceDC

    (@terrancedc)


    18 years, 1 month ago

    I’ve done some quick searches on this and I haven’t really found anything. So, I’m hoping someone out there has some ideas. My host has locked my database a couple of times because of slow queries examing something like 700,000+ rows with only 94 returned. I don’t know what that means, but here’s the snippet they sent me from the query log.

    '# Query_time: 13 Lock_time: 0 Rows_sent: 94 Rows_examined: 769333
    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;'

    Can anyone make heads or tales of this? I realize the wp_post2cat table is probabl left over from the Cat2 tag plugin I was running before upgrading to 2.0. I’m no longer runnign the plugin. Could WordPress still be querying the table? Could that be the problem? Should I drop the table?

    The only other database-related plugins I’m runnign are:

    • Akismet
    • Brian’s Latest Comments
    • Drop Down Get Links
    • Link’s Page
    • Mini-Posts
    • Nice Related Posts
    • Random Quotes

    I can disable some of these and see what happens, but if anyone knows about any related problems with any of these, it would be a big help.

Viewing 1 replies (of 1 total)
  • Thread Starter TerranceDC

    (@terrancedc)

    18 years, 1 month ago

    An update. It’s not a table for a plugin that’s causing the problem. I realized today it’s a WordPress database table. I’ve optimized it, but I’m guessing if I drop the table something 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. 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.

    My host is on me to fix the problem. I have no idea how. If there isn’t a solution soon, Im’ 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 scratch.

    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.

Viewing 1 replies (of 1 total)
  • The topic ‘Problems with Slow Queries’ is closed to new replies.