To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?

When we optimize clients’ SQL queries I pretty often see a queries with SQL_CALC_FOUND_ROWS option used. Many people think, that it is faster to use this option than run two separate queries: one – to get a result set, another – to count total number of rows. In this post I’ll try to check, is this true or not and when it is better to run two separate queries.

For my tests I’ve created following simple table:

Test data has been created with following script (which creates 10M records):

First of all, let’s try to perform some query on this table using indexed column b in where clause:

Results with SQL_CALC_FOUND_ROWS are following: for each b value it takes 20-100 sec to execute uncached and 2-5 sec after warmup. Such difference could be explained by the I/O which required for this query – mysql accesses all 10k rows this query could produce without LIMIT clause.

Let’s check, how long it’d take if we’ll try to use two separate queries:

The results are following: it takes 0.01-0.11 sec to run this query first time and 0.00-0.02 sec for all consecutive runs.

And now – we need too check how long our COUNT query would take:

Result is really impressive here: 0.00-0.04 sec for all runs.

So, as we can see, total time for SELECT+COUNT (0.00-0.15 sec) is much less than execution time for original query (2-100 sec). Let’s take a look at EXPLAINs:

Here is why our count was much faster – MySQL accessed our table data when calculated result set size even when this was not needed (after the first 5 rows specified in LIMIT clause). With count(*) it used index scan inly which is much faster here.

Just to be objective I’ve tried to perform this test without indexes (full scan) and with index on b column. Results were following:

  1. Full-scan:
    • 7 seconds for SQL_CALC_FOUND_ROWS.
    • 7+7 seconds in case when two queries used.
  2. Filesort:
    • 1.8 seconds for SQL_CALC_FOUND_ROWS.
    • 1.8+0.05 seconds in case when two queries used.

So, obvious conclusion from this simple test is: when we have appropriate indexes for WHERE/ORDER clause in our query, it is much faster to use two separate queries instead of one with SQL_CALC_FOUND_ROWS.

Share this post

Comments (66)

  • Dathan Pattishall

    Have you tried this same test under high concurrency. Say 12 active threads? I did a benchmark a while back (2 years ago using INNODB of course) under concurrency the trend of using SQL_CALC_FOUND_ROWS was a bit faster, but then again this finding may have been tainted by the INNODB scalability bug you found.

    August 29, 2007 at 12:20 am
  • Nima

    IMHO comparison is not true, in “EXPLAIN SELECT SQL_NO_CACHE count(*) FROM count_test WHERE b = 666;”, count is performed on index without access physical data. But in “EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FROM count_test WHERE b = 999 ORDER BY c LIMIT 5;”, mysql uses index to determine number of rows to examine (through compound index on b and c) and returns a result set by picking physical data which takes some time (because columns are not included in index). I think use SQL_CALC_FOUND_ROWS in queries that use complex where (for example search queries with many parameters and paging result set) is much faster than sending two queries. I use a stored procedure that accept a dynamic sql query, an offset and a limit then use prepared statement to execute query and assigns FOUND_ROW() to a output parameter then give it to paging class to build paging links. I tested that my approach is much faster than issuing two queries. because second query which accepts limit and offset do the same thing but one with SQL_CALC_FOUND_ROWS just stores number of records before restricting rows. If I am wrong, please clarify it for me.
    thanks.

    August 29, 2007 at 1:38 am
  • Maarten

    Which version of mysql server did you use? Does this apply to all current versions or is the SQL_CALC_FOUND_ROWS option more optimized in mysql 5 ?

    Depending on the availability of indexes, mysql should be able to optimize a SQL_CALC_FOUND_ROWS query in such a way that users do not need to think about using a separate count(*) query anymore.

    August 29, 2007 at 6:19 am
  • peter

    Dathan,

    The things can be well different for different situations and different storage engines. In this case there is simply much more work which needs to be done if sql_calc_found_rows is used. And it is frequently so. In some cases you may have the fact you need only one real query instead of 2 (select found_rows() does not touch data) may affect things ie if innodb had to spend a lot of time in the queue.

    August 29, 2007 at 6:37 am
  • peter

    Nima,

    There are two very different things – using index to restict amount of rows examined and using index ONLY to get all data query needs.
    For count(*) the data is not even touched, for original select it has to be accessed and accessed for each row rather than only few 5 rows which is where the difference comes from.

    There are different cases – filesort and full table scan are mentioned examples.

    use of Innodb primary key is another case worth to mention – it effectively has all columns so touching data does not add much.

    Belive me SQL_CALC_FOUND_ROWS can make things slower in many cases – if this is your case I do not know, check it what makes sense. Just do not assume it is ALWAYS faster as I’ve seen people doing.

    August 29, 2007 at 6:42 am
  • peter

    Marten,

    This issues of being unable to switch to traversing index only exists in MySQL 5.0 and 5.1 In MySQL 5.2 there are some optimizations in similar area so I have not tested if it is the case.

    August 29, 2007 at 6:43 am
  • Alexey Kovyrin

    Marten,

    All tests were performed on MySQL 5.0.45 on MacOS X 10.4.8.

    August 29, 2007 at 7:25 am
  • Ryan

    take a look at http://bugs.mysql.com/bug.php?id=18454. This was submitted off a support case I opened with mysql last year. It’s currectly marked as an enhancement

    August 29, 2007 at 9:10 pm
  • Perrin Harkins

    I tested this on an application a few months ago and found the same: SQL_CALC_FOUND_ROWS was always much slower than two queries. The reason appears to be that MySQL does a good job of optimizing LIMIT queries by stopping them when the desired number of rows has been found. Using SQL_CALC_FOUND_ROWS defeats this optimization.

    August 30, 2007 at 9:31 am
  • Sinisa Milivojevic

    I happen to be the one who has introduced this feature in the first place. Even for MyISAM there are cases when using this option is faster then two queries. For InnoDB this option is even more usefull. But in any case, every query should be checked out for few different ranges. Largest problem that I have percieved with some customers is that they forget to remove this option when (due to application change) number of rows is no longer required.

    August 30, 2007 at 10:36 am
  • peter

    Sure Sinisa, in some cases this feature makes sense – when file