Slow queries( SQL_CALC_FOUND_ROWS) bringing down site

Hi Jaybook, I thought id have a go at helping you since I thought SQL_CALC was going to be the downfall of me for 6 months, hehe.

I’m guessing you have a large database, have you? I think SQL_CALC hasn’t been addressed in wordpress because the average size wordpress blog of maybe a few hundred posts and visitors a day would have no performance issues that would need addressing and unfortunately us few that do are are a small minority.

I am no mysql and php guru but iv learnt that the SQL_CALC_FOUND_ROWS query does a full count scan in your database of your wp_posts to determine the number of rows that are in that part of the database before other queries are made.

I have an article directory with 240,000 articles so obviously every time I had a page view this query would scan the whole 240,000 rows and a page loading on average took about 5.3 seconds. If I was lucky enough to have spikes in traffic id have the same issue as you, my server would overload and crash because the mysql queries where getting backed up in the database waiting for SQL_CALC queries to finish.

The main offending SQL_CALC query is found in the /mp-includes/query.php file. I was excited to find a magical solution for the issue a few weeks back with a wordpress patch ticket found at http://core.trac.wordpress.org/ticket/10964.

My results have been amazing, page load time went from average 5.7 seconds to 1.3 seconds. And slow query log file size dropped from 50Mb to 2Mb in growth a day. Obviously this is a vast improvement and since the wordpress platform is being used more and more as a general CMS, this fix would be gold to any webmaster that has a wordpress DB on the large side.

I read that SQL_CALC is also found in the the admin files somewhere also, that explains why that is still loading slow.

Now also when it comes to caching plugins, nothing comes close to W3 Total Cache found at http://wordpress.org/extend/plugins/w3-total-cache/, WP SuperCache only caches pages while W3 Total Cache caches everything including browser, pages, database queries, minifies & gzip html, objects and also CDN’s like Amazon’s service. Its used by the big wordpress sites like Mashable.com and it also has an awesome feature that allows you debug to see what queries each of your individual pages are making when you view the source code.

Let me know how ya go with those suggestions and just let me know if you have any questions.

Jay,

Glad to hear the patch gave you a pretty dramatic drop in server load average. I to still get the SQL_CALC_FOUND_ROWS appearing in my slow log but only a fraction to what it is.

I have determined that it only performs the query,

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')  ORDER BY wp_posts.post_date DESC LIMIT 0, 100

SELECT FOUND_ROWS()

when the homepage is loaded but not in post pages. I cant seem to track down why the query is run because it doesn’t appear to be used since the results to that query are only available to the first SELECT query after the SELECT FOUND_ROWS().

My first select query after SELECT FOUND_ROWS() is

SELECT  wp_posts.* FROM wp_posts  WHERE 1 = 1  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND wp_posts.ID IN ( ID1,ID2,ID3... )  ORDER BY FIELD( wp_posts.ID,ID1,ID2,ID3... )

Does that query use the SQL_CALC_FOUND_ROWS results? I’m not sure.

The revision surely does waste a lot of space as I realized when I first started investigating my server load issues. Just wondering what query you used to delete your revisions. There are a lot of posts on the web that quote the wrong query as DELETE FROM wp_posts WHERE post_type = “revision”;. This query only deletes the revisions from wp_posts and not the meta data associated with those revisions.

This should be used.

DELETE a,b,c FROM wp_posts a WHERE a.post_type = 'revision' LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id);'

If you used the first one, you can delete the revision postmeta with

DELETE pm
FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON wp.ID = pm.post_id
WHERE wp.ID IS NULL

and can clean up relationships with

DELETE FROM wp_term_relationships
WHERE NOT EXISTS (
        SELECT * FROM wp_posts
        WHERE wp_term_relationships.object_id = wp_posts.ID
);

#WARNING for anyone reading this, backup your database before performing any DELETE query in phpmyadmin, believe me, I made the mistake of not doing it and it sucks when things go wrong.

Can someone post a fully patched query.php (i’m running WP 3.0.1) or explain how to patch it myself.
On http://core.trac.wordpress.org/ticket/10964 there are a few diffs and patches but i’ve no clue on which and how to apply them.

Thx

I also need a patch for 3.0.3 or 3.0.4. I replaced the query.php with the file list but it just gave me an error. Can someone update the patch and/or give instructions on how to implement it correct?

This is a major issue and I need help getting this fixed as soon as possible. Thank you.

I think I’m having this same issue on a less robust site. About 13,000 posts total, and a hundred or so categories. After the recent 3.1 upgrade, I instantly noticed a performance hit on two of our more query-intensive pages – pages that were quick-loading before, even without caching. Analyzing with query tools, it’s pretty clear that SQL_CALC_FOUND_ROWS is a culprit.

One query that reads like this:

$my_query1 = new WP_Query(array('category__and' => $myarray,'orderby' => title,'order' => ASC, 'post_status'=>publish, 'showposts' => 250,'ignore_sticky_posts' => 1));

results in:

SELECT SQL_CALC_FOUND_ROWS wp_posts.*
FROM wp_posts
WHERE 1=1
AND ( wp_posts.ID IN
(
SELECT object_id
FROM wp_term_relationships
WHERE term_taxonomy_id IN (3,6)
GROUP BY object_id
HAVING COUNT(object_id) = 2
))
AND wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish')
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_title ASC LIMIT 0, 250

and:

SELECT term_taxonomy_id
FROM wp_term_taxonomy
WHERE taxonomy = 'category'
AND term_id IN (6,3)

with a combined execute time of over 10 seconds.

Once cached, of course the pages load quickly. But the CPU maxing out for one measley SQL query and one page-load is pretty ridiculous. Executed on their own in a query browser, the same queries are instantaneous, but obviously PHP and WordPress itself are involved in the mix here too.

I’m at a loss for now. I’m by no means an expert PHP/MySQL/Wordpress coder, but I’ve lived and learned a bit. I would welcome any suggestions from anyone having better luck with this.

This doesn’t make sense since all big newspapers and magazines run with the same phpmyadmin database system. If thousands of newspapers can do it this must be common knowledge how to manage large databases. Might be rather something like, yes we can, but not for free?

I’ve rolled back to 3.02. After some more analysis I’m beginning to think that another problem may reside in category__and. And potentially aggravated by the fact that our site has so many categories. Running a sampling of our sql queries as generated by WP using category__and (feeding it two category IDs only), each query takes around 5 seconds to execute in the mysql workbench. Running these same queries against the pre-3.1 DB results in instant execution. Not sure what changed, but this has got to be a bug.

same boat like you guy, but i run 2.9.2 with 400k post and 1.4gb db ….

site running is ok, only problem i facing now is i can make new post, whenever i try to login wp-admin -> post/edit, then my browser is freeze, you can see from my task manager ram usage, jump from 200 – 900 and till moving until it crash …
any solution ?