Quick trick for speeding up pagination

One of my web projects, like so many others, has a series of pages where data is displayed in a table. Often this data becomes too large for a single page, and it has to be broken down into separate pages. The 5 dollar word for this is pagination, and you've seen it; you just may not have known what it was called. When a page shows links to the next and previous pages and shows you on page x of y, this is what is being done.

The way pagination is typically handled is to run two queries. One with the full clause SELECT fields, ORDER BY, WHERE, etc; and attach a LIMIT clause at the end. And a second query with SELECT COUNT(x) with the same where clause, but nothing else. The first query gives you a single page of data, while the second query gives you the total number found.

To me, this seems a inefficient technique; albeit a very common one. So, I asked whether it could be sped up over at Webmasterworld and was pointed in the direction of SQL_CALC_FOUND_ROWS and FOUND_ROWS().

Before we go any farther, these functions require version 4.0 or later of MySQL. They are not available in versions prior to that; and I honestly do not know if they are available in other DBMS packages, as I don't use anything else. I will also add that I typically develop web sites in PHP, although these benchmarks should apply to any language.

Note: All queries were repeated 100 times. The times I show are the average.

Background & Benchmarking
The table I am querying has a total of 899 records and 27 fields. The field that will be used in the where clause is a pipe delimited text field with unique ids that are tied back to a separate table. The other table is not relavent, as I search on the id and not the description stored in the other table. A LIKE clause with wild cards is used in the WHERE clause.

The primary query returns 232 records and took 0.014918 seconds. This particular query was done for benchmarking only:

The second benchmark query added a LIMIT clause and took 0.002326 seconds. This time it returned a single page of data, but did not return the total number.

Actual tests
For each of the actual tests, I ran two queries. The first was to get a single page of data. The second was to get a total count of all records matching the WHERE clause.

Test #1 - This is the method that is on most of the sites I found. The main query is run including the ORDER BY and LIMIT clauses. The second query is run, but instead of SELECT *, SELECT(id) is used and the ORDER BY and LIMIT clauses are left out.

Test #2 - This method uses the FOUND_ROWS() function. Note the addition of SQL_CALC_FOUND_ROWS in the query before the field list.

Test #1 took 0.01047 seconds while test #2 took 0.008999 seconds. Admittedly, we're talking about a very small difference in actual time. But, as a percentage the second method saves 10%. And 10% can add up when a query pair has to be run thousands of times per day.

Conculsion
The reason I originally looked into this was that I had a series of queries with multiple LIKEs in the WHERE clause using ANDs and ORs. Benchmarking these queries showed them to be relatively slow, and I needed to find any way I could to pull more speed out. But, leaving out the pagination or total page count was not an option. While I have not tested this on more complex queries, I believe that the difference will be more significant the more complex the query becomes. If & when I test it with more complicated queries, I will update this page, or add another.

But, even on simple queries, it is possible to save clock cycles and time; which is important to anyone working on web sites.

You will also notice that I used SELECT * in all the queries. I know this is not recommended, and I rarely use it in actual programs. But, for these tests, I wanted to make it as much of a 'worst case' as possible.

And, for more information on how I benchmarked these queries, see my page on PHP Benchmarking.