Menu
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?
638
The theoretical maximum number of rows in a table is 264 (18446744073709551616 or about 1.8e+19). This limit is unreachable since the maximum database size of 281 terabytes will be reached first.
Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second. Transaction speed is limited by the rotational speed of your disk drive.
The query optimizer in SQLite has basically two choices on how to implement this query. (There are actually six different choices, but we will only consider two of them here.) Pseudocode below demonstrating these two choices. The same indexes are used to speed up every loop in both implementation options.
The SQLite3 database will just return the single record that you wanted directly. So your "sequential file" is 50,000 times slower than SQLite3 at retrieving data.
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; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
