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:
SELECT * FROM table WHERE field LIKE '%searchstring%' ORDER BY lastname ASC, firstname ASC
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.
SELECT * FROM table WHERE field LIKE '%searchstring%' ORDER BY lastname ASC, firstname ASC LIMIT 0, 10
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.
SELECT * FROM table WHERE field LIKE '%searchstring%' ORDER BY lastname ASC, firstname ASC LIMIT 0, 10 SELECT COUNT(id) FROM table WHERE field LIKE '%searchstring%'
Test #2 – This method uses the FOUND_ROWS() function. Note the addition of SQL_CALC_FOUND_ROWS in the query before the field list.
SELECT SQL_CALC_FOUND_ROWS * FROM table WHERE field LIKE '%searchstring%' ORDER BY lastname ASC, firstname ASC LIMIT 0, 10 SELECT FOUND_ROWS()
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.
Be First to Comment