MySQL: Pagination - SQL_CALC_FOUND_ROWS vs COUNT()-Query

Permanent Link: MySQL: Pagination - SQL_CALC_FOUND_ROWS vs COUNT()-Query 17. Juni 2009 RSS Feed for comments on RSS-Feed für Kommentare zu: MySQL: Pagination - SQL_CALC_FOUND_ROWS vs COUNT()-Query 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.

11 comments


09.05.2013, 17:38 o'clock

näsplastiks Gravatar

näsplastik
24.05.2013, 09:22 o'clock

We are a group of volunteers and opening a new scheme in our community.
Your site offered us with valuable info to work on.
You have done a formidable job and our entire community will be thankful to you.


07.06.2013, 09:05 o'clock


07.06.2013, 09:05 o'clock

Andreys Gravatar

Andrey
11.06.2013, 16:00 o'clock

You have 1 problem: you query have CACHE.
If you will send SQL_NO_CACHE then its was true.
But - on first time you query with COUNT was uncached. Second Count - getting from CACHE.
(See my result time on 1-st Select and 2-nd Select (0.137 + 0.011) WHERE t2.`step` = 5). In you select with LIMIT 50,50 and LIMIT 100,50 have first time NO cache, but when you check COUNT - have cache.



I check all in myself tables
t1 = 60k rows
t2 = 600k rows


SELECT SQL_CALC_FOUND_ROWS t1.`test_name`
FROM `a_test_1` AS t1
LEFT JOIN `a_test_2` AS t2
ON t1.`test_id` = t2.`items_id`
WHERE t2.`step` = 5
LIMIT 50,50;
Execution Time : 0.137 sec

SELECT FOUND_ROWS();
Execution Time : 0.011 sec


SELECT SQL_CALC_FOUND_ROWS t1.`test_name`
FROM `a_test_1` AS t1
LEFT JOIN `a_test_2` AS t2
ON t1.`test_id` = t2.`items_id`
WHERE t2.`step` = 5
LIMIT 100,50;
Execution Time : 0.011 sec

SELECT FOUND_ROWS();
Execution Time : 0.010 sec

//////////////////////////////////////////////
Here you see with CACHE (second Select(LIMIT 100,50) was cached Execution Time : 0.137 sec vs 0.011 sec
//////////////////////////////////////////////

SELECT SQL_NO_CACHE t1.`test_name`
FROM `a_test_1` AS t1
LEFT JOIN `a_test_2` AS t2
ON t1.`test_id` = t2.`items_id`
WHERE t2.`step` = 3
ORDER BY t1.`test_name` ASC
LIMIT 100,50;

Execution Time : 0.087 sec

SELECT SQL_NO_CACHE t1.`test_name`
FROM `a_test_1` AS t1
LEFT JOIN `a_test_2` AS t2
ON t1.`test_id` = t2.`items_id`
WHERE t2.`step` = 3
ORDER BY t1.`test_name` ASC
LIMIT 50,50;

Execution Time : 0.095 sec


SELECT SQL_NO_CACHE COUNT(*)
FROM `a_test_1` AS t1
LEFT JOIN `a_test_2` AS t2
ON t1.`test_id` = t2.`items_id`
WHERE t2.`step` = 3
ORDER BY t1.`test_name` ASC;
Execution Time : 0.128 sec

SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS t1.`test_name`
FROM `a_test_1` AS t1
LEFT JOIN `a_test_2` AS t2
ON t1.`test_id` = t2.`items_id`
WHERE t2.`step` = 3
ORDER BY t1.`test_name` ASC
LIMIT 100,50;
Execution Time : 0.280 sec

SELECT SQL_CALC_FOUND_ROWS t1.`test_name`
FROM `a_test_1` AS t1
LEFT JOIN `a_test_2` AS t2
ON t1.`test_id` = t2.`items_id`
WHERE t2.`step` = 3
ORDER BY t1.`test_name` ASC
LIMIT 100,50;
Execution Time : 0.280 sec


SELECT FOUND_ROWS()
Execution Time : 0.010 sec


27.06.2013, 18:09 o'clock


05.09.2013, 17:12 o'clock

Antoines Gravatar

Antoine
25.10.2013, 04:09 o'clock

If you are going for best contents like I do, simply visit this site all the time since it
provides quality contents, thanks

www.varasfadu.com.ars Gravatar

www.varasfadu.com.ar
11.01.2014, 18:42 o'clock

Hello, i feel that i saw you visited my blog thus i came to
go back the favor?.I am attempting to find issues to enhance my website!I suppose
its good enough to make use of a few of your concepts!!

free spins vid registrerings Gravatar

free spins vid registrering
17.02.2014, 21:21 o'clock

Roulette bord finns i många kasinon över hela free spins casino sätta sina satsningar i dem,
har trögheten i solen och planeterna höll dem snurra i miljarder år sedan.
Jag skrev om, och som jag nämnde tidigare, kan du göra
det snabbt.

pinedwteyros Gravatar

pinedwteyro
17.08.2014, 11:37 o'clock

To the extent arms makers are affected by economic swings, many are late-cyclical as they have long delivery times and long-running contracts with governments. tn pas cher homme Chevron missed analyst forecasts in its fourth quarter by a sizeable gap, turning in earnings per share at US$2.58 per share compared to the average prediction of US$2.84, despite higher oil prices. jordan pas cher homme In the current quarter, the local state-owned asset regulator will encourage companies, including SAIC Motor Corp and Shanghai Tunnel Engineering Co, to restructure. The city has a target to increase the asset securitization ratio in local government-owned firms to 35 percent this year from 30.5 percent in 2010. abercrombie paris The Centre for Economics and Business Research (CEBR) predicts bonuses for 2012 in London's financial sector will more than halve to 1.6 billion pounds (US$2.5 billion) from last year, and will keep falling until 2015.
Zhongjin Gold rose 1.65 percent to 33.23 yuan. Gold rose to four-week high yesterday in New York as US dollar slumped while US equities also declined, which boosted demands for the precious metal as an alternative asset. chaussure tn pas cher A separate report from the Labor Department showed consumer prices edged higher in February. The Consumer Price Index rose 0.5 percent last month, slightly stronger than forecasts. Core prices, which exclude food and fuel costs, edged up 0.2 percent, the same as the previous month. Banks were told to make less money available for lending on Thursday. The reserve requirements will rise by 0.5 percentage point from next Wednesday, the People's Bank of China said. hollister femme In the first eight months, the country's imports surged 30.2 percent on an annual basis to US$1.13 trillion, faster than exports, which climbed 24.5 percent. hollister sale outlet August 24: Jobs announces his resignation as CEO and is replaced by chief operating officer Tim Cook, but stays on as Apple board chairman.
Oil prices spiked at this time last year with uprisings in several Middle East nations, particularly Libya. Investors who worried about major disruptions in supplies bought oil and drove up the price. WTI is up 21 percent from a year ago, when it sold for around US$85 a barrel. Brent is up about 15 percent. escarpins louboutin Japanese officials fear that the yen's near 5 percent surge in the past month will harm the economy, which skidded into its second recession in three years following the March 11 earthquake and tsunami. mulberry outlet store The company, based in Taoyuan, northern Taiwan, will purchase as many as 20 million of its own shares by September 17, according to filings to the Taiwan stock exchange on Saturday. Half of the repurchased shares will be transferred to employees, and the remainder canceled. abercrombie france soldes Poly Real Estate sank 4.66 percent to 10.43 yuan. China Vanke tumbled 4.43 percent to 7.55 yuan. China Merchants Property Development lost 3.33 percent to 18.27 yuan. fitflop shoes During the four years, about 370.8 billion yuan were spent on mining resource exploration, up 110 percent year on year. Of the total, central and local government funds took up 15.3 percent, while the rest came from social investment, he said.
http://ramblingsofazoologist.com/2014/02/07/nature-quest-visit-a-new-reserve/
http://ubuntuhandbook.org/index.php/2013/07/canon-drivers-for-ubuntu-and-linux-mint/#comment-768234
http://www.mconnectsolutions.com/blog/tablet-ecommerce-website-next-development-in-mobile-commerce-mcommerce/?rcommentid=831874&rerror=incorrect-captcha-sol&rchash=ac12e6f55ae8308a1bb0d62d9659742e#commentform
http://mesh411.com/first-cr-bard-mesh-trial-underway-in-wv/#comment-691
http://www.chezcarrcuisine.com/shrimp-and-scallop-ceviche/#comment-3011

Write a comment

(will not be published)