MySQL: Get total number of rows when using LIMIT    August 11, 2006    49

Every 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.

49 Comments

Nissius September 9th, 2013 at 8:45 pm

Muito Obrigado!
Me ajudou muito!
—————
Thanks

Brian January 25th, 2012 at 1:16 am

Radness. Thanks.

diansunday December 28th, 2011 at 11:57 am

I am so confuse :D

Oucil December 1st, 2011 at 4:03 am

Brilliant, been using the classic method for a while and never bothered to look for an alternative until tripping across this.

+1 to @Aris regarding millions of rows, but even those with smaller datasets will see a benefit when running very complex queries… in my instance it was actually impossible to use the classic method, due to extra custom query vars in the statement that were required in the joins.

This just saved the day! literally ;)

Helen Neely November 29th, 2011 at 1:25 am

Thanks for this tips. I recently started working with mysql and PHP, and paging through result was what I needed. But in a more complex fashion as I had lots of joins.

This piece helped a lot.
Nice call.

Ayac November 9th, 2011 at 4:19 pm

Thanks for the helpful post! It worked on my news website. However the difference is not so big, just imagine when you have 100 users with the same Content Management System engine that requesting DB rows… you can multiply the speed you would loose using non optimized code and you will find a significant difference right there…

Vishal June 30th, 2011 at 10:09 am

sorry for Posting without thinking. It will help you in first time query for count.

Vishal June 30th, 2011 at 10:02 am

This way is not usefull at all in pagination case. because in pagination we have to fetch total count first and then find out page number user is on and then calculate limit position. Hence I think we should use count way and store count in session/cookie for 5min. then again reset count by querying after 5min. this will avoid overhead of count query

Diego June 28th, 2011 at 6:08 pm

Cara muitoooooooooo…. obrigado….
Já tem meses que eu estou atrás disto….

Thanks so much.

coupons June 11th, 2011 at 2:29 pm

Yeah, I have not found a way to get around it.
Generally I run a select count(*) and then run the proper query with a limit.
If you run the same queries often, you could try using disk cache or mem cache to store them and store the 2 data sets (count and data) together for faster access next time.

rajesh April 28th, 2011 at 11:29 am

Superb posting

Envias December 12th, 2010 at 12:50 pm

@RutgerB

Or you can just use

list( $totalrows ) = mysql_fetch_row( mysql_query( ‘SELECT FOUND_ROWS();’ ) );

Rozliczanie nfz December 9th, 2010 at 11:59 pm

Thank You for this article. It helped me a lot !

many thanks form Poland ;)

ACD May 11th, 2010 at 4:12 pm

@Yousef: Try this with a 1,000,000 row set, and you will see the difference, especially if your PHP machine is not the same as your MySQL machine. The network transport can be very significant and PHP might go out of memory.

Yousef May 4th, 2010 at 12:50 am

Hello,

While thinking about it, if you are going to execute at least one query anyway, then get the fields you want, and let your application do the LIMIT in your PHP , c#, or any language you want. It’s very easy to skip printing N rows when you are going to write code to calculate the right values for executing the LIMIT query.

So why bother executing two queries when you are doing the whole job of calculations (next page number, how many rows, and whether to display next/prev button)?

I did it in my application, you just iterate rows that fall in the range that you wish, while skipping all other rows.

SQL execution time is equal to the first counting query.

Blessings.

senthil March 26th, 2010 at 10:39 am

pls help me

Error in WHERE clause near limit.
Unable to parse query text.
when i execute this cmd error occured
SELECT * from a WHERE name = se limit 25

in sqldatasource1 when i execute query bilder at a time only occured

asp.net mysql odbc5.1 connection

thanks

senthil

Anonymous March 26th, 2010 at 10:01 am

Error in WHERE clause near ‘limit’.
Unable to parse query text.
when i execute this cmd error occured
SELECT * from a WHERE name = ‘se’ limit 25

in sqldatasource1 when i execute query bilder at a time only occured

asp.net mysql odbc5.1 connection

thanks

senthil

254 February 5th, 2010 at 12:32 pm

dasflj fadljkfalkja

Anonymous November 24th, 2009 at 9:16 pm

fhj

anirudha November 6th, 2009 at 8:02 am

i like this theme how i can design our blog in blogspot on anirudhagupta.blogspot.com

pauL September 1st, 2009 at 3:44 am

Thanks so much!
Your post allowed me to get right to the point and save a lot of time. Just what i needed.

Daniel Guzman August 31st, 2009 at 4:26 am

I found this information to be extremely useful. It has made a (positive) difference for my web project. Thank you!

kelly August 7th, 2009 at 3:22 pm

I tried this in simple query browser

SELECT SQL_CALC_FOUND_ROWS NAME FROM core_users WHERE NAME LIKE ‘b%’ LIMIT 10;

SELECT FOUND_ROWS();

but this give me 249 results where as there is only 68 results without limit :(

sudip August 3rd, 2009 at 11:29 am

hi, i m trying to show the no of records in my database tabse table in the php page, but i cant succsss. can any body there plz help me………….

eg: totoal records = 50

sudip August 3rd, 2009 at 11:24 am

hieloo

Danny May 19th, 2009 at 11:19 am

Hey,

Thanks for the quick and helpful post.
I have a fairly complex query (at least for my skills) which uses GROUP BY, so I was stumbling on my own feet trying to get a LIMITless count, even with using a secondary query.
This is a great solution, thanks for sharing.

MYSQLLover January 18th, 2009 at 9:46 am

Iam Using MYSQL 5.0.45-community-nt, I do not get the number of rows, instead i get number of colums as a result when i execute the below query. Can any one help me.

SELECT SQL_CALC_FOUND_ROWS * FROM toppers LIMIT 5;
SELECT FOUND_ROWS();

Tusar November 18th, 2008 at 9:19 am

good

co November 2nd, 2008 at 1:33 pm

Simple solution:

rs.absolute( -1 ); //move cursor to last row
int rowCount = rs.getRow(); //get the current row# = last row = row count

This code has side effect of moving the cursor position but of course you can enhance this code to firstly save the current cursor position then move it back when finished.

donny October 15th, 2008 at 8:30 pm

I’m new to PHP and recently setup my local machine with PHP and MySQL for doing development. I was sort of stuck when I needed to post my work for the user to test and review. After looking around a bit I found a site that hosts PHP and MySQL apps. I was surprised that it was free – it seems they’re offering the service at no cost until 2012. At that point they’ll change over to a fee-based service. However, in the meantime, it’s a great place to do anything from demo and sandbox right up to posting sites for real.

Their pitch is as follows:

“This is absolutely free, there is no catch. You get 350 MB of disk space and 100 GB bandwidth. They also have cPanel control panel which is amazing and easy to use website builder. Moreover, there is not any kind of advertising on your pages.”

Check it out using this link:

http://www.000webhost.com/83188.html

Important: There’s one catch in that you must make sure you visit the account every 14 days – otherwise the account is marked ‘Inactive’ and the files are deleted!!!

Thanks and good luck!

bonta_kun June 24th, 2008 at 5:46 pm

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.

kigan May 29th, 2008 at 6:43 am

Is it possible for me to count the number of rows after the select command …i am new here ..can any one tell me

RutgerB June 25th, 2007 at 9:18 pm

Nice article, exactly what I was looking for.

To use in php:

$row = mysql_fetch_row(mysql_query(“SELECT FOUND_ROWS()”));
$total = $row[0];

Aris March 14th, 2007 at 12:34 pm

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.

Emily September 7th, 2006 at 7:25 pm

Good info. Track back in my web site.

Basti August 15th, 2006 at 11:51 am

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.

MacFly August 15th, 2006 at 11:06 am

@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 ;)

Basti August 14th, 2006 at 4:57 pm

@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.

MacFly August 14th, 2006 at 11:10 am

“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.

Basti August 11th, 2006 at 8:16 pm

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.

Gordon August 11th, 2006 at 7:38 pm

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.

Add a Comment