Efficient paging in SQLite with millions of records

by

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;

More Related Contents:

  1. How do I unlock a SQLite database?
  2. “Insert if not exists” statement in SQLite
  3. How to delete or add column in SQLITE?
  4. How to use ROW_NUMBER in sqlite
  5. Android – Pulling SQlite database android device
  6. How to get Top 5 records in SqLite?
  7. Insert new column into table in sqlite?
  8. How do you enable LIMIT for DELETE in SQLite?
  9. SQLite Schema Information Metadata
  10. How to create custom functions in SQLite
  11. Sqlite group_concat ordering
  12. How to get a list of column names
  13. is there a limit to the size of a SQLite database?
  14. How to generate all dates between two dates
  15. Does it matter what extension is used for SQLite database files?
  16. sqlite – How to get INSERT OR IGNORE to work
  17. How do I DROP a constraint from a sqlite (3.6.21) table?
  18. How do I add a new column in between two columns?
  19. Creating stored procedure and SQLite?
  20. How Scalable is SQLite? [closed]
  21. change sqlite file size after “DELETE FROM table”
  22. Are there any reserved words in SQLite?
  23. What is the difference between related SQLite data-types like INT, INTEGER, SMALLINT and TINYINT?
  24. Modify a Column’s Type in sqlite3
  25. Case sensitive and insensitive like in SQLite
  26. SQLite accent-insensitive search
  27. How to display blob value using x’abc’ binary string literal syntax?
  28. sql query – how to apply limit within group by
  29. Unable to load DLL sqlite3 in Universal Windows App running on Mobile
  30. Creating stored procedure in SQLite

Leave a Comment