Better MySQL pagination

Posted on January 15, 2016  •  1 minutes  • 165 words

Consider this

SELECT * from Bookings LIMIT 5000,10

versus this

SELECT * from Bookings
INNER JOIN (Select id FROM BOOKING LIMIT 5000,10) AS result USING (id)

My Bookings table has merely 6000 records yet the first query takes approx ~10 seconds which is outrageous. Luckily, we can optimize this using late lookups like in query 2.

In the second query, we select id from Bookings and then join the original table back. This will make each individual row lookup less efficient but the total number of lookups will be reduced by a lot.

Also, if you could pass more condition into the select, it will greatly improve the performance as well. So instead of making your paging url like this example.com/products?page=10, you can use example.com/products?page=10&last_seen=1023. From that, you can pass WHERE id > 1023 into the pagination query making the whole thing a lot faster.

Those are what I used for optimizing pagination. If you know any other, please let me know.

Peace out, everybody.