wp sql query: takes too long: how to figure out why?

  • I have a low traffic site with 2000 pages. I run thesis theme framework and I use 4 widgets https://www.imaginaryplanet.net/weblogs/idiotprogrammer/ and 2 simple plugins. I put 10 posts on the main page and 18 teasers.

    i got this message from my web hosting service (see below).

    Any idea what might be causing this?
    Also, would wp-super-cache reduce the number of queries?

    Thanks.

    ******************************************

    The database is “rjnagle_jen” on “web27.hosting.com” and the query is:
    SELECT SQL_CALC_FOUND_ROWS b2posts.* FROM b2posts WHERE 1=1 AND b2posts.post_type = ‘post’ AND (b2posts.post_status = ‘publish’) ORDER BY b2posts.post_date DESC LIMIT 0, 10

    A query that takes so long is usually a sign that
    something isn’t optimized and the query is clogging
    the server and impacting other users.

    From April 12th 2010 we will be killing all MySql
    SELECT queries that take over 1 min unless you
    contact us first and explain to us why you need
    to run a query for longer.

Viewing 3 replies - 1 through 3 (of 3 total)

  • I would check a couple of things:

    1. If you have made a lot of changes to posts, you might have a lot of revisions that take up query time. See this link for how to remove them.
    2. Does your posts table need to be optimized? See this link.

    I think it’s the use of SQL_CALC_FOUND_ROWS which appears to impose problems for some setups, unfortunately it’s written into the main query class(WP_Query) that runs throughout wordpress, inside the get_posts method, so it’s pretty much stuck being like that..

    Short of no longer using WordPress or changing setup, i can’t see what you can do… it is an integral part of the query class, it’s used to work out the total result set and setup paging.

    There’s alot of differing opinions around regarding SQL_CALC_FOUND_ROWS and it seems to boil down to some setups showing really poor performance and others on the contrary and getting better results.

    Take a look through these various links (i’ve been having a read myself), and there’s no clear cut answers(i feel) as to why results are terribly poor for some users/setups, and great for others.

    If you can use phpMyAdmin, or a similar tool, you can test that query with and without the SQL_CALC_FOUND_ROWS to see if that is the problem. If it is, like t31os_says, there isn’t much you can do short of changing hosting services. In any case, optimizing the database might help.

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘wp sql query: takes too long: how to figure out why?’ is closed to new replies.