How to efficiently paginate large datasets with PHP and MySQL?

Question

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.

Answer 1

First of all, if you want to paginate, you absolutely have to have an ORDER BY clause. Then you simply have to use that clause to dig deeper in your data set. For example, consider this:

SELECT * FROM my_table ORDER BY id LIMIT 20

You'll have the first 20 records, let's say their id's are: 5,8,9,...,55,64. Your pagination link to page 2 will look like "list.php?page=2&id=64" and your query will be

SELECT * FROM my_table WHERE id > 64 ORDER BY id LIMIT 20

No offset, only 20 records read. It doesn't allow you to jump arbitrarily to any page, but most of the time people just browse the next/prev page. An index on "id" will improve the performance, even with big OFFSET values.

Answer 2

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 ;-)