MySQL: Get total number of rows when using LIMIT August 11, 2006
Posted by Slobodan Kovacevic in : Programming , trackbackEvery now and then you need to limit the number of rows MySQL returns, i.e. use the LIMIT clause. Result set pagination is by far the most often usage of LIMIT clause, since you usually want to select only rows you’ll be displaying on certain page.
The problem is that for pagination you also need total number of rows in a result set, so you know how many pages you’ll have. This usually means that you need to execute query two times. First query is for counting total number of rows without LIMIT. Second query is exactly the same as the first, just without LIMIT and it will actually retrieve required data. You would need two queries like these:
SELECT COUNT(*) FROM users WHERE name LIKE 'a%'; SELECT name, email FROM users WHERE name LIKE 'a%' LIMIT 10;
Now, this is not such a big problem when you have small result sets and/or simple queries. But if you have a complex query that joins several tables and takes a while to execute - well, you probably wouldn’t want to execute it twice and waste server resources.
Luckily since MySQL 4.0.0 you can use SQL_CALC_FOUND_ROWS option in your query which will tell MySQL to count total number of rows disregarding LIMIT clause. You still need to execute a second query in order to retrieve row count, but it’s a simple query and not as complex as your query which retrieved the data.
Usage is pretty simple. In you main query you need to add SQL_CALC_FOUND_ROWS option just after SELECT and in second query you need to use FOUND_ROWS() function to get total number of rows. Queries would look like this:
SELECT SQL_CALC_FOUND_ROWS name, email FROM users WHERE name LIKE 'a%' LIMIT 10; SELECT FOUND_ROWS();
The only limitation is that you must call second query immediately after the first one because SQL_CALC_FOUND_ROWS does not save number of rows anywhere.
Although this solution also requires two queries it’s much more faster, as you execute the main query only once.
You can read more about SQL_CALC_FOUND_ROWS and FOUND_ROWS() in MySQL docs.
Comments»
http://archives.neohapsis.com/archives/mysql/2005-q4/2128.html
Unfortunately, using SQL_CALC_FOUND_ROWS has the nasty consequence of blowing away any LIMIT optimization that might happen. So you’re back at square one - in order to count the number of rows, MySQL still has to populate the entire result set into the filesort or temporary table.
It does have the benefit of making the code cleaner, but may not really help in performance the way I was hoping it would.
I am not quite sure about the performance claims on Neohapsis mailing list. In MySQL documentation it says:
“If you are using SELECT SQL_CALC_FOUND_ROWS, MySQL must calculate how many rows are in the full result set. However, this is faster than running the query again without LIMIT, because the result set need not be sent to the client.”
This sounds quite logical. Whenever you perform a query (with or without LIMIT) MySQL still needs to select appropriate rows, order them and then apply limit you set. Now it might use indexes and stuff like that to make it go faster, but still you need to do it twice.
[…] AS Workshop » MySQL: Get total number of rows when using LIMIT “since MySQL 4.0.0 you can use SQL_CALC_FOUND_ROWS option in your query which will tell MySQL to count total number of rows disregarding LIMIT clause.” (tags: mysql tips) […]
“However, this is faster than running the query again without LIMIT, because the result set need not be sent to the client.”
Besides the limit query, you use a count query, what is not a complex result set. The SELECT FOUND_ROWS() should perform similar.
@MacFly - count query doesn’t have complex result set, but it still needs to perform exactly same query as it just did. Additionally, you’ll still have a lot of rows in data set. For example, if you have total 6000 rows you’ll have 6000 rows too in count query, even though you might only retrive the first row as all others are the same.
@Basti - as the docs says, using sql_calc_found_rows mysql is faster only because of not sending the result set to the client. but either case, you don’t need the second result set.
for example (for a 6000 rows table):
old method: select count() - you get one row result set, then select * limit 25 - you get 25 rows
new method: select SQL_CALC_FOUND_ROWS * limit 25 - you get 25 rows, then SELECT FOUND_ROWS() - you get one row
roughly, either case the client get 26 rows of data.
The only way to be faster if the server needs fewer resources for calculaing this pseudo column, but the docs says nothing about that, so it’s time to benchmark
Well, I did try to execute query which yields around 6500 rows with data from 5 tables. As far as I can tell speed was something like this… (note, that my local MySQL server had some strange time results - i.e. when executing same query once it had time of 0.5 sec and then next would be less then 0.03 sec for same query - I presume it might have something to do with some query caching or something)…
Anyway, I found that query with limit executes VERY fast in less then 0.05 sec. Same query without limit and only with COUNT(*) executed roughly in 0.3 sec. This is roughly the same time as the query with limit, but with SQL_CALC_FOUND_ROWS “turned on”. In the end FOUND_ROWS() query took literally no time to execute.
So, in the end it appears that it’s almost the same (unless 0.05 of a second matters to you - and if it matters to you then you need way more testing than this). As I said this wasn’t exactly an extensive and precise benchmark, but I think it was precise enough.
[…] AS Workshop » MySQL: Get total number of rows when using LIMIT Once again MySQL displays exactly why I love it so much. It’s SO developer friendly. Here’s a quick tip to get te total number of rows in a result set without using SELECT COUNT(*) (tags: mysql tip) […]
Good info. Track back in my web site.
I have a table with 2.500.000 records. The SQL_CALC_FOUND_ROWS method is 2 secs faster than the ‘classic’ one, so it is definately much faster. With tables that don’t have many rows though the difference is not significant.
[…] es gibt nur die mglichkeit einer zweiten abfrage, allerdings kannst du das ja mit SQL_CALC_FOUND_ROWS kombinieren. AS Workshop MySQL: Get total number of rows when using LIMIT MySQL AB :: MySQL 3.23, 4.0, 4.1 Reference Manual :: 12.10.3 Information Functions mnsch, liest hier eigentlich nie jemand die anleitungen? s’Amstel __________________ Help stamp out and abolish redundancy! Help stamp out and abolish redundancy! Beitrge: 2.000 (2,85 Beitrge pro Tag) *freu* […]
Nice article, exactly what I was looking for.
To use in php:
$row = mysql_fetch_row(mysql_query(”SELECT FOUND_ROWS()”));
$total = $row[0];
[…] via ArrayStudios […]
[…] Kilde: MySQL: Get total number of rows when using LIMIT […]
Is it possible for me to count the number of rows after the select command …i am new here ..can any one tell me
exactly what I was looking
I was using sessions before so that you don’t have to query each time you change page. but the problem was, what the table got updated. then I will miss those updates.
I also tried 2 queries but it got messy
with this i just have to add SQL_CALC_FOUND_ROWS to each query string and use SELECT FOUND_ROWS(); after passing the result to a variable.