Efficient paging in SQLite with millions of records

Question

I need to show the SQLite results in a list view. Of course, I need to page the results.

The first option is to use the LIMIT clause. For example:

SELECT * FROM Table LIMIT 100, 5000

It returns records 5001 to 5100. The problem is that internally SQLite "reads" the first 5000 records and it is not too efficient.

What is the best approach for paging when there are a lot of records?

Answer 1

Please note that you always have to use an ORDER BY clause; otherwise, the order is arbitrary.

To do efficient paging, save the first/last displayed values of the ordered field(s), and continue just after them when displaying the next page:

SELECT *
FROM MyTable
WHERE SomeColumn > LastValue
ORDER BY SomeColumn
LIMIT 100;

(This is explained with more detail on the SQLite wiki.)

When you have multiple sort columns (and SQLite 3.15 or later), you can use a row value comparison for this:

SELECT *
FROM MyTable
WHERE (SomeColumn, OtherColumn) > (LastSome, LastOther)
ORDER BY SomeColumn, OtherColumn
LIMIT 100;