use of SQL_CALC_FOUND_ROWS needs to be eliminated

[_ck_]

It's long overdue to rewrite a small part of query.php, eliminating the use of SQL_CALC_FOUND_ROWS and switching to COUNT(). Doing so will result in significant performance gains on active sites.

SQL_CALC_FOUND_ROWS has serious performance degradation issues and it's effects was even experienced on wordpress.org over two years ago before it was removed from bbPress.

More here with links to documentation and benchmarks:
​http://ckon.wordpress.com/2009/07/22/wordpress-still-uses-the-nasty-sql_calc_found_rows/

My first rough attempt at a workaround that might be feasible:
​http://pastebin.com/f45e1f9f6

[Denis-de-Bernardy]

there was a recent discussion on this one, in a separate ticket, and it was found that SQL_CALC_FOUND_ROWS was faster...

[Denis-de-Bernardy]

See in particular these two comments:

​http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/#comment-490258

[_ck_]

Except the queries I am seeing WordPress do with SQL_CALC_FOUND_ROWS are not significantly complex and do not typically use JOINS as meta is appended in a separate query, so COUNT() will still be faster in most cases.

Typical example, benchmark it on a large site

SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS  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')  ORDER BY wp_posts.post_date DESC LIMIT 25,25;

SELECT FOUND_ROWS();

vs.

SELECT SQL_NO_CACHE COUNT(0) 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

Typically COUNT(*) is also inferior to a single column COUNT(0) but my original workaround idea was a rush job.

I assure you I am seeing SQL_CALC_FOUND_ROWS do bad things in a real-world environment in WordPress. Turn on the slow query log on any busy site and you'll see for yourself.

[_ck_]

It took some digging because of trac's poor search but I found the previous tickets and your work related to this: #7415 #9642

It seems like the discussion was never finished or followed through.

[dd32]

I think some investigating+profiling is needed of the WordPress SQL queries, SQL_CALC_FOUND_ROWS may be faster than COUNT() on some queries, and vice versa, but they need to be applied direectly to the WordPress generated queries as well.

Also, While that example doesnt include any joins or complexities, The Query API can easily generate something with taxonomy joins (categories) or added clauses(archives) or joins (Meta tables) or a combination.. IMO it should be optimized for the most common use-cases and the least-performing cases should potentially look for alternate methods perhaps?

Milestone: Future Release for long-standing "low priotity" defects, Current-trunk for all other defects, point release back-porting at commiters discretion. (Generic reply for when I change the milestone)
Component: Tempted to change to Performance/Optimization as well, Not really a Query "bug" as such..

[_ck_]

Thanks for investigating further. Be sure to use realworld database sizes if possible of course.

In theory you could have the best of both worlds, ie. keep the option for SQL_CALC_FOUND_ROWS in the core as a fallback and work around it using a separate COUNT() query when you know it will be faster. This is how bbPress 1.0 currently handles it, since SQL_CALC_FOUND_ROWS was folded back in it's core via backPress.

I suspect this other idea is outside the realm of hope for a near-future improvement, but in theory the total query count should be stored per session, that way during pagination the more expensive total rows query is not repeated on every page load for that user.

[Rand HOPPE]

Just replying to add my interest in this issue.

I have a standard WP install with user blogging daily for four years, nothing fancy. 7500+ records in wp_posts

Yet slow query log always presents SQL_CALC_FOUND_ROWS for this DB.

Doesn't make sense.

[pixelgeek]

Just wanted to add my two cents to this. SQL_CALC_FOUND_ROWS is causing my server to become unresponsive every day or two.

I've been tracking down some performance issues on the server and the one issue I am left with is the huge load that the SQL_CALC_FOUND_ROWS in query.php cause on a regular basis. Especially during searches.

Perhaps my site is not in the normal scope of a standard WP site. I get about 16,000 visitors a day and generates from 450-500,000 pageviews a month.

I keep my database optimized and the codebase up to date but my mysql-slow.log file is littered with these calls and it does eventually bring my machine down. Routinely.

This seems to be a very slow mySQL call and it would be great if there was a way to speed the pagination of the site up by using a more efficient call

[scribu]

A different (and not properly tested) approach: #10964

Also note that as of WP 3.1, meta queries also use JOINs.

Perhaps the optimal approach would be to use COUNT(*) when $join is empty.

[scribu]

Going to close as duplicate of #10964 since it has patches and more discussion.