Slow Query

  • Hi,
    I am running WP 2.7.1 on my site and from past few days, I see the following type of query (which is very slow) almost once in an hour.

    SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1 AND (wp_posts.post_author = 58) AND wp_posts.post_type = ‘post’ AND (wp_posts.post_status = ‘publish’) ORDER BY wp_posts.post_date DESC LIMIT 0, 10

    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 (‘840’, ‘841’, ‘842’, ‘843’) 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 20, 10

    Can someone please tell me from where these queries are coming? I dont have any plugins installed except for WP Super cache, akismet and Page Navi.

Viewing 5 replies - 1 through 5 (of 5 total)
  • Moderator Samuel Wood (Otto)

    (@otto42)

    www.ads-software.com Admin

    Well, the first one looks like a normal author archive page. The second one looks like a category archive (on the third page of the archive, 10 posts per page). Neither one is particularly unusual, normal usage could cause them to appear.

    Also, those queries should not be particularly slow, as the database is indexed to optimize for them, mostly.

    Thread Starter venkatsiddareddy

    (@venkatsiddareddy)

    Thanks a lot.
    They are not very slow. It is taking some where around 2 sec. But they are appearing in the Slow Query list.

    I will check my theme if there is a way to optimize them.

    Thanks

    This turns into a big problem on a large scale site.

    Query time turns to 6 in my case. Anybody know how to optimize this query?

    DB Cache was the anwser to my problems. Site can now handle severe user loads now.

    We have the same problem with the same queries. We have a site getting about 500k pageviews a day. We have it nailed down to having a large number of posts (60,000 total) in a single category (14,000). Any time you do a GROUP BY or ORDER BY, mysql has to create temp tables to do the sort operations. Since it’s a write-op, query caching has no affect. Plus any time you do an update to a post in that particular category, it invalidates cache, and forces users to an unprimed cache page, thus resulting in harder hit in the db load. We’re getting hit with 200+ concurrent users, each trying to hit that same query, which backs up in the db, and eventually crushes the server.

    DB Cache will work if you’re on a single server, but if you’re on a multi-server, then you need something like memcached.

    We still haven’t nailed down fix just yet, other than to take the 14000 posts down to 2000, so the queries aren’t hanging in mysql. If you are or have access to a DBA that can tune the buffers on MySQL, I’m sure you can alleviate this problem, but other than that, haven’t found an exact solution just yet to stop this particular query from killing you under heavy load.

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘Slow Query’ is closed to new replies.