how to repeatedly retrieve certain amount of data from a large data set?

Question

I have a INVENTORY table. There are many items inside. When I query it, I just want to retrieve 50 items each time, and continue to get next 50 if user request and repeat this process until end of records. I am using MySQL database. How to do it in plain SQL if possible? thanks,

Answer 1

Use LIMIT:

SELECT ... FROM ... LIMIT <offset>, <number>

In your case <number> would be 50 and <offset> would increase by 50 each request.

Answer 2

SELECT * FROM Inventory
WHERE whatever
ORDER BY something
LIMIT 50 OFFSET 0

gets you the first 50; the second time, use OFFSET 50; the third time, use OFFSET 100; and so on. It can be a problem if the table changes between requests, of course; if you do have that problem, solving it can be costly (force locks, make a copy of the table, or other unpleasant solutions -- there is no magic).

If your client just cannot remember the offset it last used, you can store it in a separate auxiliary table, but it does complicate the SQL a lot.