MySQL: Pagination - SQL_CALC_FOUND_ROWS vs COUNT()-Query

Permanent Link: MySQL: Pagination - SQL_CALC_FOUND_ROWS vs COUNT()-Query 17. Juni 2009 Comment No Comment

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.

MySQL server has gone away

Permanent Link: MySQL server has gone away 1. April 2009 Comment No Comment

Today we had a database crisis due to a powercut. Apart from corrupted tables (because of the immediate "shutdown") we also had another problem: CLI scripts/cronjobs suddenly threw a MySQL server has gone away Exception after a really short amount of time (maybe 30 seconds)

I found this useful list, the only problem was: all the points didn't match for my case. So we came up with an own list that we worked through:

  • Flush privileges
  • Restore the User grants/privileges
  • Restart the MySQL server
  • dpkg-reconfigure mysql-server-5.0 (or similar)

In our case restarting the MySQL server fixed the problem.

Don't use NOW() in MySQL SELECT Queries

Permanent Link: Don't use NOW() in MySQL SELECT Queries 18. März 2009 Comment One Comment

You maybe want to show all the products that have been added to your database in the last 7 days on your high-traffic website. You are using MySQL and your query probably looks like this:

SELECT *
FROM product
WHERE created >= NOW() - INTERVAL 7 DAY

Although being correct, this query is a bad query, because it is using NOW(). NOW() returns the current date and time to the split second. In other words: This query will never land in the cache query!

The better way would be to read the current day in PHP and pass it on to the query:

$date = date('Y-m-d');
$sql = '
SELECT *
FROM product
WHERE created >= "%s" - INTERVAL 7 DAY
';
$sql = sprintf($sql, $date);

Now we have a SQL query that perfectly lands in the cache query.