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 5000, 100

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?

Pastiche

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;

Bemire

What about a case where you have 101 identical values in SomeColumn? This seems to be better: blog.ssokolow.com/archives/2009/12/23/… – Charwoman

@JacekŁawrynowicz If the sorting column is not unique, you need to sort by more columns. Anyway, if you have an alternative answer, create an answer. – Bemire

@CL if i want to do this with join query how to do with multiple AND conditions – Santossantosdumont

@Santossantosdumont Nowadays, you can use row values. – Bemire

The problem using this approach is briefly explained by this comment – Counterpoise