1

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?

David542
  • 104,438
  • 178
  • 489
  • 842

1 Answers1

2

Using OFFSET is usually not a good idea as the database has to skip this many rows before returning a result.

A good solution is to have an index for the product column and SELECT ... FROM ... WHERE product BETWEEN _start_ AND _end_

And keep changing start and _end so that the desired page is returned by the database.

If this is difficult, say, you have lots of rows with identical product you can define a composite key (product, xxx) that better selects rows and use that to select the page.

If all else fails and you decide to create a new table for paging -- add an autoincrement id column and page using that key.

nimrodm
  • 23,081
  • 7
  • 58
  • 59
  • thanks for this suggestion. I like being able to generalize it by adding the autoincrementing id and not having to worry about creating composite indexes. How would I auto-create autoincrement id field when doing the insert, for example: `CREATE TABLE queryset_cache AS SELECT * FROM mytable ORDER BY product;` ? – David542 Sep 23 '19 at 20:20
  • Turns out `sqlite` has a `rowid` property for every table. You can try using that (i.e., no need to add another column and you can use `CREATE .. AS ..`) – nimrodm Sep 23 '19 at 20:23
  • right, it does, but that will often be 'out of order' when doing the insert. That was actually the first thing I tried using. Anyways, I've accepted your answer and asked a new questions here: https://stackoverflow.com/questions/58069710/how-to-insert-a-query-into-sqlite-with-an-autoincrementing-value-for-each-row – David542 Sep 23 '19 at 20:24
  • Not sure why it's out of order, but you can always manually create the table with an autoincrement column and then `INSERT INTO new_table SELECT ... FROM old_table` – nimrodm Sep 23 '19 at 20:28
  • sorry I was wrong here. It can be done that way which makes this much simpler! – David542 Sep 23 '19 at 20:42