Take the 2-minute tour ×

Given any query (grouping, joins, and subqueries are all possible in any combination), I'd like to break it up with pagination as efficiently as possible. Right now I just run the query, then count the number of rows returned to PHP, figure out how many pages of data that is, display the ones that go on the current page, and output the proper links to the other pages.

Seems to me that the potentially very large result set from the query is mostly wasted however. Is there a way to run the query twice where the first time you select COUNT(*) to get a single number with the rows to be paginated and the second time you run the original query with proper limits based on the page currently being viewed? Would this be significantly less taxing on the database server?

For a trivial query this is no problem... replace everything after SELECT and before WHERE with count(*). However this doesn't work if there is grouping in the query, as you'll get the count in each group, not the total number of rows. If you're grouping on columns a, b, and c is it correct to select count(distinct a, b, c)? Does anything special need to be done to handle subqueries?

share|improve this question
add comment

5 Answers

up vote 3 down vote accepted

You probably could use next:

select count(*)
from (<original select query>)
share|improve this answer
I like the simplicity of that method. Is there any risk of bad performance with this? Seems safe enough... –  Ty W Jul 15 '09 at 13:58
ended up going with this due to the potential race condition of SQL_CALC_FOUND_ROWS with multiple simultaneous requests –  Ty W Jul 15 '09 at 15:05
Sure there's risk of bad performance - this query can't be much faster than original one, especially when there are some having type clauses (checking aggregate values etc). Well, in SQL land there's always risk of bad performance :) –  Arvo Jul 15 '09 at 18:12
add comment

You can use SQL_CALC_FOUND_ROWS to give you your small result set, but also have MySQL calculate the size of the unlimited result set:


share|improve this answer
never knew about SQL_CALC_FOUND_ROWS before. not sure I'll be able to use it due to the methods we have to use in order to query the DB but it works well on the command line. another tool is always a good thing! –  Ty W Jul 15 '09 at 14:10
add comment

This may help: mysql has limit feature:

select * 
from mytable
limit 10,40

Where limit 10,40 means give me 40 results starting with result 10 (which is 0 based).

You could build pagination on this feature. In the example above, 40 means 40 results/page.

share|improve this answer
I understand that, however you have to somehow get the total number of matching records to build proper pagination. –  Ty W Jul 15 '09 at 13:45
True, that part is not covered in my answer. It's just a starting point. –  Mercer Traieste Jul 15 '09 at 13:48
add comment

You can ask mySQL to calculate the number of rows that would have been returned without the limit clause by using SQL_CALC_FOUND_ROWS.

This will give you the first page of 20:


Then, you can ask for the number of rows that would have been return without SQL_CALC_FOUND_ROWS:


This will be answered from within the driver without another request to the database server. It should also work with more complex queries.

share|improve this answer
add comment

I would experiment a bit in your development environment to see if there's any performance benefit.

You could limit the return result based upon records-per-page using the LIMIT clause as MercerTraieste suggests, however, you're now making a trip to the database per page, which is usually something to avoid. I assume your PHP script uses a cursor to get data from the MySQL server, and I imagine the cursor stays open to retrieve that data for your other pages. By doing LIMITed queries, you might actually tax the database server more. Of course, I would test/experiment to find out by monitoring database load using a MySQL performance monitor tool, if there is one.

You probably won't even have to do a COUNT(*) to paginate them, just determine how many records you want per page, and just return them starting from different result points.

I.e., do (psuedocode follows)

SELECT <columns> FROM <table> LIMIT <records-per-page>;
while (More data available for future pages)
    records-shown += records-per-page;
    SELECT <columns> FROM <table> LIMIT <records-per-page>,<records-shown>;

Admittedly, other ways of doing it are likely cleaner.

share|improve this answer
add comment

Your Answer


