SELECT * from Bookings LIMIT 5000,10
SELECT * from Bookings INNER JOIN (Select id FROM BOOKING LIMIT 5000,10) AS result USING (id)
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
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.