programmatically replace SELECT fields in sql query to determine number of results

Question

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?

Answer 1

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.

Answer 2

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:

SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name LIMIT 10;

SELECT FOUND_ROWS();

Answer 3

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:

SELECT SQL_CALC_FOUND_ROWS * FROM table LIMIT 0,20

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

SELECT FOUND_ROWS() AS numrows

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

Answer 4

You probably could use next:

select count(*)
from (<original select query>)

Answer 5

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.