php developer blog

MySQL: Pagination - SQL_CALC_FOUND_ROWS vs COUNT()-Query

by Dominik Jungowski

17. Juni 2009 comments feed

When using pagination for let's say a list of offers that where never clicked, you have to know the exact amount of offers (which were never clicked) in order to know how many pages you have. Now there are 2 possible ways of calculating the exact amount of offers: You can use either SQL_CALC_FOUND_ROWS or you can setup a second query with a COUNT() in it. I did the tests with SQL_NO_CACHE in order to get the best results possible. The clicks table has about 18.000.000 rows, the offer table about 800.000. Let's start with some time results.

Using SQL_CALC_FOUND_ROWS:

mysql> SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS o.offer_id
    -> FROM offer AS o
    -> LEFT JOIN clicks AS c
    -> ON (o.offer_id = c.offer_id)
    -> WHERE c.offer_id IS NULL
    -> LIMIT 50,50;
50 rows in set (30.84 sec)

mysql> SELECT FOUND_ROWS();
1 row in set (0.00 sec)

Using a second query with COUNT():

mysql> SELECT SQL_NO_CACHE o.offer_id
    -> FROM offer AS o
    -> LEFT JOIN clicks AS c
    -> ON (o.offer_id = c.offer_id)
    -> WHERE c.offer_id IS NULL
    -> LIMIT 50,50;
50 rows in set (0.03 sec)

mysql> SELECT SQL_NO_CACHE COUNT(o.offer_id)
    -> FROM offer AS o
    -> LEFT JOIN clicks AS c
    -> ON (o.offer_id = c.offer_id)
    -> WHERE c.offer_id IS NULL;
1 row in set (30.97 sec)

At a first glance they look equally fast, both taking about 30 seconds. But: They are only equally fast, when query caching is turned off. Let's assume we're on a high-traffic website where performance matters, so we turn the query cache on. MySQL Query caching is like a key-value cache with the key being the EXACT query and the resultset being the value. Once we turn on the cache, the pagination is way faster with the second query using COUNT().

Why?

When using SQL_CALC_FOUND_ROWS the application has to calculate the found rows every single time we turn the page, because the query changes, while the COUNT()-Query always remains the same, meaning that its result comes from the query cache from the second time on. Let's emulate:

Using SQL_CALC_FOUND_ROWS:

mysql> SELECT SQL_CALC_FOUND_ROWS o.offer_id
    -> FROM offer AS o
    -> LEFT JOIN clicks AS c
    -> ON (o.offer_id = c.offer_id)
    -> WHERE c.offer_id IS NULL
    -> LIMIT 50,50;
50 rows in set (31.13 sec)

mysql> SELECT FOUND_ROWS();
1 row in set (0.00 sec)

mysql> SELECT SQL_CALC_FOUND_ROWS o.offer_id
    -> FROM offer AS o
    -> LEFT JOIN clicks AS c ON (o.offer_id = c.offer_id)
    -> WHERE c.offer_id IS NULL
    -> LIMIT 100,50;
50 rows in set (30.71 sec)

mysql> SELECT FOUND_ROWS();
1 row in set (0.00 sec)

Using a second query with COUNT():

mysql> SELECT o.offer_id
    -> FROM offer AS o
    -> LEFT JOIN clicks AS c
    -> ON (o.offer_id = c.offer_id)
    -> WHERE c.offer_id IS NULL
    -> LIMIT 50,50;
50 rows in set (0.03 sec)

mysql> SELECT COUNT(o.offer_id)
    -> FROM offer AS o
    -> LEFT JOIN clicks AS c
    -> ON (o.offer_id = c.offer_id)
    -> WHERE c.offer_id IS NULL;
1 row in set (31.11 sec)

mysql> SELECT o.offer_id
    -> FROM offer AS o
    -> LEFT JOIN clicks AS c
    -> ON (o.offer_id = c.offer_id)
    -> WHERE c.offer_id IS NULL
    -> LIMIT 100,50;
50 rows in set (0.04 sec)

mysql> SELECT COUNT(o.offer_id)
    -> FROM offer AS o
    -> LEFT JOIN clicks AS c
    -> ON (o.offer_id = c.offer_id)
    -> WHERE c.offer_id IS NULL;
1 row in set (0.00 sec)

Et Voilà! While the SQL_CALC_FOUND_ROWS Queries took more than one minute together, the queries with the second COUNT() query only took a bit more than 30 seconds together, meaning they are twice as fast.

I'm pretty sure there are situations where SQL_CALC_FOUND_ROWS is the way to go, but in cases like this one you definately wanna go for the COUNT()-Query.