How to efficiently paginate large datasets with PHP and MySQL?
logic, mysql, pagination, php
Solution
A solution might be to not use the limit clause, and use a join instead -- joining on a table used as some kind of sequence.
For more informations, on SO, I found this question / answer, which gives an example -- that might help you ;-)
Problem
As some of you may know, use of the LIMIT keyword in MySQL does not preclude it from reading the preceding records. For example: ``` SELECT * FROM my_table LIMIT 10000, 20; ``` Means that MySQL will still read the first 10,000 records and throw them away before producing the 20 we are after. So, when paginating a large dataset, high page numbers mean long load times. Does anyone know of any existing pagination class/technique/methodology that can paginate large datasets in a more efficient way i.e. that does not rely on the LIMIT MySQL keyword? In PHP if possible as that is the weapon of choice at my company. Cheers.