(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:
Remember how they changed the login cookie in WordPress 2.5 ?
Then they realized they got the security model wrong so they changed it again in 2.6 causing more backward compatibility problems. (then they finally added HttpOnly in 2.7)
So since it’s all working/stable now, guess what, they are tampering with it again: