Why is my `sql_calc_found_rows` faster than `count(*)` in MySQL?

Question

I know it has been discussed here that sql_calc_found_rows in MySQL is considered slower in many cases than running 2nd count(*) query. Moreover, we don't really have choice now, since sql_calc_found_rows is deprecated and will be removed from future versions of MySQL. But in my query, which consists of several left joins, multiple dynamic where clauses with limit and offset, adding sql_calc_found_rows with subsequent FOUND_ROWS() query has no overhead according to my tests. However, running count(*) as a second query with no limit and offset clause, to get number of total rows found, nearly doubles execution time. It seems very puzzling to me how every other source asserts that count(*) is faster due to internal optimizations, but in my practice it is considerably slower. It is worth noting that I have no indexes at this point. I would very much appreciate if anyone could bring a bit of clarity into this matter.

Answer 1

It still depends.

Since you want a general assessment, not specific to your query: You cannot make the general assumption that one of the methods will be faster. MySQL will treat each query indiviually, and if it finds a faster execution plan, it will use it.

Adding a limit can shorten the execution time if MySQL doesn't need all rows to give you the requested result.

• A trivial example is select * from table limit 1. MySQL can take the first row and then stop, as opposed to reading the whole table.

• A trivial counterexample is select * from table order by some_unindexed_column limit 1. MySQL has to read the whole table, order it, then return the first row.

• A trivial countermeasure to make the counterexample fast again is to add an index on some_unindexed_column. MySQL can now read the first entry from the index, read the corresponding row from the table and stop. (The two steps here are making this query slightly slower than the first one though).

Obviously, if adding a limit does not affect execution time (where I don't include the effect of having to send less rows to the client), sql_calc_found_rows doesn't affect it either.

Assessing the execution time for the related count(*)-query is harder to do generally, as it usually is just a different query, which is not directly comparable anymore. E.g. for select count(*) from (select * from table order by some_unindexed_column) sub, the order by is obviously superfluous, and you would just remove it if you write it by hand, and even if not, MySQL will, depending on your version, actually remove it itself.

So you would be actually running select count(*) from table.

Completely coincidental, this is also the related count(*)-query for select * from table limit 1

This count-query is faster than:

• select * from table order by some_unindexed_column as it doesn't need to do the ordering
• select * from table order by some_unindexed_column limit 1 and select sql_calc_found_rows * from table order by some_unindexed_column limit 1 as well as it doesn't need to do the ordering (and sql_calc_found_rows has no effect for this case)

It is slower than: - select * from table limit 1 as it has to read all rows, whereas this query can stop after the first row - select * from table order by indexed_column limit 1 as it has to read all rows, while the limit query can stop early using the indexed(!) column

A special, and probably only interesting, case, closely related to the situation in the blog you read, is

select sql_calc_found_rows * from table order by indexed_column limit 1

This query will read the index, then use the index information to read the data from the original table (as it needs all columns). This 2-step-process is significantly slow. It is actually so slow, that MySQL itself can decide to not use an index if it suspects that it has to read a considerable percentage of rows from the original table. The select count(*) from table on the other hand does not need this 2-step-lookup.

This is basically the most relevant general case where a count-query performs better, and is exactly what happened in your blog. YMMV, but the mentioned performance gain of factor 10 is possible this way, although it's usually less.

There is still a way to mitigate this problem: if you add a covering index. If you e.g. have an index (a,b,c) and only select from those, e.g. select b, c from table order by a, this can happen completely within the index and does not need the 2-step-lookup.

Another case is a situation where count(*) allows MySQL to pick the smallest possible index. E.g. for a select count(*) from table, MySQL can pick any available index on that table, and for a small index, you need to read less bytes. Which is faster then reading more bytes. (I'll ignore that for MyISAM-tables, the count is stored in a table and can be looked up instantly).

There are probably a more situations (or things like select very_expensive_function(a) from table, which obviously gets faster if you do not executing the function), which can give you a benefit or not.

To sum up:

• if a limit does not change the execution speed, adding sql_calc_found_rows has no effect, and running an additionally count(*) query will obviously just add more time.
• if a limit does change the execution speed, then adding sql_calc_found_rows will make it slower. If running an additionally count(*) is faster than adding sql_calc_found_rows depends on the query.

It is worth noting that this analysis was for a really trivial query. The more complex your query gets, the less likely it is that the count-version is (just by luck) faster than sql_calc_found_rows. It will also depend on your MySQL version.

According to your description, your query currently falls in the first category, and if MySQL does not find a way to optimize the count-version on it's own, it is expected that it will take about the same time as the original one (which will just double the total time for both queries). Anything more specific (and especially if you need help optimizing your query) requires a look at your specific query.