Efficient MySQL database queries for pagination
Heres a nice little known gem of SQL to help with selecting limited amounts of records from a table. This can be used to pull paginated recordsets from a MySQL database efficiently.
Usually when I do pagination, I do a query on the database and limit the output using
The problem with this is that the whole query is executed and the results filtered out for you by ColdFusion.
I have only ever used the MySQL LIMIT clause with one parameter e.g : LIMIT 10 to return only 10 results but I didnt know you could tell it to start at a specific row and return the next 10 records.
So, the above can be cut down to:
which basically puts all the work on the database and tells it to fetch 10 records from the database starting at record 20.
The ORDER BY is important because the 20 is the row index so it will order the table first and then start at row 20.
A small tip but a valuable one none the less.
Tags:
Comments [6] - Leave a comment
Comments
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=850