Sqlite Query Optimization (using Limit and Offset)

Question

Following is the query that I use for getting a fixed number of records from a database with millions of records:-

select * from myTable LIMIT 100 OFFSET 0

What I observed is, if the offset is very high like say 90000, then it takes more time for the query to execute. Following is the time difference between 2 queries with different offsets:

select * from myTable LIMIT 100 OFFSET 0       //Execution Time is less than 1sec
select * from myTable LIMIT 100 OFFSET 95000   //Execution Time is almost 15secs

Can anyone suggest me how to optimize this query? I mean, the Query Execution Time should be same and fast for any number of records I wish to retrieve from any OFFSET.

Newly Added:- The actual scenario is that I have got a database having > than 1 million records. But since it's an embedded device, I just can't do "select * from myTable" and then fetch all the records from the query. My device crashes. Instead what I do is I keep fetching records batch by batch (batch size = 100 or 1000 records) as per the query mentioned above. But as i mentioned, it becomes slow as the offset increases. So, my ultimate aim is that I want to read all the records from the database. But since I can't fetch all the records in a single execution, I need some other efficient way to achieve this.

Answer 1

As JvdBerg said, indexes are not used in LIMIT/OFFSET. Simply adding 'ORDER BY indexed_field' will not help too.

To speed up pagination you should avoid LIMIT/OFFSET and use WHERE clause instead. For example, if your primary key field is named 'id' and has no gaps, than your code above can be rewritten like this:

SELECT * FROM myTable WHERE id>=0     AND id<100     //very fast!
SELECT * FROM myTable WHERE id>=95000 AND id<95100   //as fast as previous line!