I have an e-commerce website with hundreds of thousands of products in a table called products. One of the columns in the table is for the price of products.
I constantly fetch new records from the table as the users scroll down. However, pagination via limit & offset is quite expensive as you know. I have quite complicated queries but I will simplify the issue with the below query:
select * from products order by price limit 100 offset 300
One option could have been to get the last loaded product's price and use that price in the next query to load the next batch. Something like below:
select * from products where price > 999.99 order by price limit 100 offset 300
The issue however is the database contains many products with the same price. Hence, it is not possible to use the where clause to continue from where I was left off.
I could do (lower the price slightly)
select * from products where price > 999.98 order by price limit 100 offset 300
to make sure that I do not miss a product but then I do not know where I am in the overall order.
What is the best practices for such a scenario? I use mysql.
Assume you have already fetched the first set of products. You would use the last fetched product’s price and id to fetch the next set.
SELECT *
FROM products
WHERE (price, id) > (999.99, last_product_id)
ORDER BY price ASC, id ASC
LIMIT 100;