Suppose I have the following query in SQLite:
SELECT * FROM mytable ORDER BY product ASC
I would like the end user to be able to view the data in a scrollable manner (for example, like the currently-viewable data in Excel). If the user scrolls down 100 results, instead of doing something like:
SELECT * FROM mytable ORDER BY product ASC LIMIT 50 OFFSET 100
I want to make sure that the results returned to them are under 25ms or so, instead of having them wait for the time it takes to execute the query every time they move out of the viewable range.
What would be the best way to do this? In pseudo-code, I was thinking along the lines of the following:
-- 1st time query is run
SELECT * FROM mytable ORDER BY product ASC LIMIT 50 -- in main thread
INSERT INTO queryset_cache -- in background thread
SELECT rowid, * FROM mytable ORDER BY product ASC
-- 2nd time query is run, offset 84,500 (user scrolls down to that position
SELECT * FROM queryset_cache ORDER BY rowid LIMIT 50 OFFSET 84500
Would this be the best way to accomplish what I'm trying to do? Or is there a standardized way to do something like the above in SQLite? If so, what would be the best way to do this?