WordPress still uses the nasty SQL_CALC_FOUND_ROWS
(update: please don’t try this patch as is on WP versions newer than 2.8 – I am only leaving it here as a suggestion if some coder in the future decides to tackle the problem)
We’ve known for over two whole years now that SQL_CALC_FOUND_ROWS did nasty things in bbPress 0.8
It was fixed by mdawaffe (Michael) after discovering how SQL_CALC_FOUND_ROWS caused an overload on the Automattic wordpress.org forum servers due to a MySQL bug.
But to this very day, it’s still used in all WordPress versions, up to and including 2.8.2
(Even more ironic, now that bbPress 1.0 has switched to the backPress core which is based on WordPress, SQL_CALC_FOUND_ROWS is back inside bbPress, though it works around the bug)
SQL_CALC_FOUND_ROWS is typically three times slower than using COUNT() on the same query without LIMIT and ORDER restrictions.
I’ve seen at least one slow-log for MySQL that is FULL of SQL_CALC_FOUND_ROWS queries from a large WordPress installation on a dedicated server that took 11-15 seconds per query (and crashed MySQL, repeatedly).
Here is my quick & dirty patch to the WordPress (and BackPress) core that attempts to change SQL_CALC_FOUND_ROWS to the COUNT() workaround – it’s inside wp-includes/query.php – it’s tested working but not heavily tested so use with caution and let me know if you have improvements?
My changes are against the file from WP 2.5.1 but the file has barely changed since 2.5, even 2.8 is virtually the same so it should be easy to modify other versions.
There is one other use of SQL_CALC_FOUND_ROWS left in WordPress but it’s in the admin section so I am not going to worry about it for now.
More about the MySQL bug due to it’s poor optimization:
http://bugs.mysql.com/bug.php?id=18454 (cache)
http://bugs.mysql.com/bug.php?id=19553 (cache)
http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/
_ck_ says... 
Sorry, but could you be a bit more specific about the lines you have changed?
Thank you.
August 15, 2009 at 4:31 pm
Also, i use 2.7.1 unfortunately :(, so its a bit hard to understand your work.
August 15, 2009 at 4:32 pm
Don’t attempt this patch unless you know how to read/write PHP and MySQL.
August 16, 2009 at 12:33 am
Well, although it doesnt seem like that from my dumb comments i can, and i understand your code now. I just wasnt familiar with the SQL_CALC_FOUND_ROWS and SELECT FOUND ROWS()…
Now i changed it, with your code, and also with a version of mine. One of the versions worked, and used significantly less queries, also a tiny bit faster page load (and this is a fresh blog with just 50-100 posts), but unfortunately, it has broken the pagination of the WP_PAGENAVI plugin. There was no pagination at all.
I modded it further and pagination was back, but it was wrong, it displayed 4 times higher maximum pages number.
If i can figure out whats wrong, past it here.
Cheers
August 16, 2009 at 4:43 am
This patch won’t reduce queries, it just makes one kind of query significantly faster in some cases.
If you see a reduced query count for a page when you attempt to hack this in, then you’ve done something wrong and one of the queries isn’t being executed at all (most likely the full query count) hence the messed up navigation and potentially other more serious problems.
I hoped you at least backed up your database before you attempted this.
The WP devs might re-examine this problem for the next version of WordPress so I’d wait to see what they come up with instead.
August 16, 2009 at 9:33 am
You are absolutely right, it was a stupid thing to think it would reduce queries, i dont now why i thought that. Maybe cause im coding day and night and im proper tired.
Basically, i cant upgrade my wordpress version because ive put 1000+ (yes, more tha na thousand…) hours into hacking the wordpress core to suit my needs and stuff…
Anyway, i did a little research and i read that newer MySQL versions has better optimized sql_calc_found_rows… Is it true?
Still i like the idea better of running two queries than runnin this sql_cacl_found_rows…
I always database + full file backup everything before modification so theres no harm…
Cheers mate 🙂
August 21, 2009 at 4:36 pm
Pingback: In eigener Sache: Pottblog zieht am Montagmittag auf neuen Server um » Pottblog
Um… You da best. This query is killing our site. I’ll be testing this out soon.
May 18, 2010 at 11:37 pm
I had to modify the count() query to just use the id field, count($wpdb->posts.*) doesn’t work.
Other than that, it’s a good improvement.
May 19, 2010 at 12:14 am
Unfortunately i can’t find anything more actual about this problem but it still exists in 2.9.2 I’ve tested your fix which basically works but seems to return a wrong count or no count as the back/forward links in category pages don’t work anymore after applying this fix.
A shame that wordpress guys add some nasty gallery features instead of working out better performance 😦
June 7, 2010 at 11:40 am
I’ve also just implemented this fix in my 2.9.2 install and have lost prev/next links for my front, tag, and category pages. _ck_, could you perhaps take another look at this issue? My WordPress install produces several slow queries (>1 min) a day and that’s the only reason we have downtime. I’d really appreciate an update.
June 12, 2010 at 1:59 pm
Please don’t use this fix if you don’t know how to code in PHP and MySQL – I am sure it’s out of date for WP versions newer than 2.8
I stopped using WordPress @ version 2.5 because it got far too bloated so I am not familiar with the database changes in 2.9 and 3.0 – they are constantly changing and breaking things so it’s likely this fix will not work as is.
June 12, 2010 at 2:03 pm
I know. Would you have any idea how your fix might be changed to not break prev/next links? My queries are just so slow that I’d rather lose pagination than continue having downtime.
Thanks for putting this up in the first place!
June 12, 2010 at 2:07 pm
If you do this fix wrong, it’s breaking more than prev/next links.
Revert back to the old file, asap.
Your pages are probably not slow from just this problem alone, you probably have an extreme number of queries from badly designed plugins and other un-optimized WordPress routines at this point.
You’d have to install a plugin to track how many queries and where they are coming from like this one http://vapourtrails.ca/downloads/jeromes-query-diagnostics.zip
But then you’d have to do a great deal of work to get them optimized.
Many older/larger WP installs that I come across are using several dozen queries per page which is insane, it should not be more than a dozen, two dozen at the extreme.
At a minimum make sure you install a page cache like “wp-super-cache” or “1 blog cacher”.
June 12, 2010 at 3:05 pm
I’ve done all that and more. Sadly, it really is down to this.
If I figure something out I’ll report back.
June 12, 2010 at 3:07 pm
Unfortunately I am too busy to work on WordPress internals right now, especially with 3.0 about to come out which will probably break it again.
But there are hundreds of WordPress coders, you should be able to find someone who can do it, WordPress isn’t exactly rare or obscure.
June 12, 2010 at 3:09 pm
I have a site with 50,000 posts and it goes down like a brick whenever the caches i have in place have to be purged. I’ve narrowed the problem down to my theme. But there’s nothing i can do because it uses the same standard wordpress queries to grab posts that the default ones do.
My slow queries log show SQL_CALC_FOUND all over the place. Imagine looking up 200,000 rows to find 10 just so it can show an archive page. madness.
What’s even more scary is reading trac and seeing you argue with someone who says it’s been discussed and found that SQL_CALC is faster.
How the hell do big sites like mashable.com, do it?
I may have to switch from wordpress. 😦
September 11, 2010 at 5:07 pm
Mashable uses heavy duty caching and I suspect a dedicated mysql server. But they almost certainly suffer the same fate when the cache is defeated.
The problem may have been reduced in the newest mysql versions (5.1+) but this is just a complete guess, untested and unproven.
I’ve been practically shouting over the years about WP that “the emperor has no clothes” (ie. about the bloat and performance regressions) but no-one listens or seems to care.
September 11, 2010 at 5:20 pm
ck, after posting and searching trac. I found another ticket
http://core.trac.wordpress.org/ticket/10964
after a couple of minutes (i’m new to all this pretty much) ..i figured out how to apply the latest trunk patch to my 3.0.1 test site. All went well, no errors. So i added it to the live site and so far it’s good. I’ve flushed the caches serveral times now and the server barely notices. Previously, it would have died.
I have to wait until tommorrow morning when my traffic is highest.
I still have some slow queries though, but not nearly as bad.
September 11, 2010 at 6:07 pm