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.

2006-10-20 01:41:00.0

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: ColdFusion | MySQL | Tips
Add to your del.icio.us    DIGG This!    Technorati Cosmos Link    Post to Reddit    Add to your Furl    Add to Blinklist
Comments [6] - Leave a comment

Comments

Michael said:
Is there any way to do this using sql 2000 or access?
sumeet said:
This page is good but I need the pagination in jsp. Pls give the code to me.
Garve said:
Wish it was available in Access - however this slightly more longwinded version says it'll do the trick.

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=850
Eshquia said:
Very helpful. Was just starting to do this myself. Saved me some major time. Thanks.
Jm said:
how is this little known? it's well documented in the mysql manual and samples available when searching google for mysql and pagination.
@JM I dont know how little known, maybe this post will help spread it out there ;-)