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 https://core.trac.www.ads-software.com/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 https://www.ads-software.com/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.

Hello hayzie,

thank you very much for your detailed reply and explanation. Yes, we have tens of thousands of posts on this site.

I applied the patch to 2.9.1 to our 2.9.2 installation, and within a minute, the load average on the database server dropped from around 200 to 0.62. So obviously, there is a serious flaw in WordPress, or MySQL (or both), when it comes to sites with more than a thousand posts.

I also deleted all revisions older than the current month from the database, which reduced the size of the wp_posts table by more than half. Storing revision data takes up more space than necessary. More information about that can be found here:
https://lesterchan.net/wordpress/2008/07/17/how-to-turn-off-post-revision-in-wordpress-26/

I still notice periodic spikes in traffic due to some SQL_CALC_FOUND_ROWS queries which have not been addressed by the patch. These are:

SELECT?SQL_CALC_FOUND_ROWS?wp_posts.ID?
FROM?wp_posts?WHERE??=??AND?wp_posts.post_type?=???
AND?(wp_posts.post_status?=??)?
ORDER?BY?wp_posts.post_date?
DESC?LIMIT??,??

Average time: 52 seconds

SELECT SQL_CALC_FOUND_ROWS
wp_posts.ID
FROM wp_posts
WHERE ?=?
AND wp_posts.post_type = ? AND
(wp_posts.post_status = ?)
ORDER BY wp_posts.ID ASC LIMIT ?, ?

Average time: 58 seconds

SELECT YEAR(post_date) ASyear, MONTH(post_date) ASmonth`, count(ID) as posts
FROM wp_posts
WHERE post_type = ? AND post_status = ?
GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC`

Average time: 48 seconds

Re: W3 Total Cache
We tried W3 Total Cache with memcached, but it proved to be buggy / ineffective. For whatever reason, it was simply not caching the vast majority of queries. We replaced it with WP Super Cache (which duplicates cache files on each web server, instead of using a single memcached server), and the response time for pages improved to about 1/3rd or 1/4 of the original. This is totally the opposite of what I thought would be the case, and I’m not sure why it made such a difference.

We are also using MySQL on EC2, with its data stored on an EBS volume ( as described here: https://aws.amazon.com/articles/1663?_encoding=UTF8&jiveRedirect=1 )
Since EBS is essentially a networked file system, I’m not sure if that adds to the response time / locks associated with the use of SQL_CALC_FOUND_ROWS. We could use EC2 instance storage, which is much faster, but also volatile. All instance storage is permanently lost if the instance fails, or is terminated.

The takeaway from this for me is that WordPress has some serious flaws in its core code that should be addressed. The code in query.php is extremely dense, and more complicated than it should be.

Best Regards,
– Jay

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.

After all those years isnt there meanwhile a plugin doing this job of clearing the database cache of unwanted entries?

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

Thx

Just noticed i’m running 3.0.4 instead of 3.0.1 ??
And also found out how to patch things using the patch command. Just need to know which patch I can apply to the 3.0.4 installation.

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 was also using W3 Total Cache (I was using it for nearly 8 months) but I started to notice significant slowdown this week that I originally thought it was our host. After two days of not knowing the problem, I turned of W3 and notice speed came back up. I have since reverted back to WPSuperCache. As Jay mentioned, it was becoming buggy and ineffective.

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.

I have the exact same problem. Large site, ~200k posts.

Patching query.php seemed to help a lot up until 3.0.4, but since upgrading to 3.1 we’re having big problems with mysql performance – even with a patched query.php (from https://core.trac.www.ads-software.com/ticket/10964).

Like for k-factor above, cached pages are delivered fine, but the CPU often is maxing out for a single query.

I’m at a loss.

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?

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.

Yeah, it has got to be a bug. The database server has gone absolutely haywire since upgrading to 3.1, patch or no patch.

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 ?