> When I have 10000 records in the table, it takes 20 ms to fetch 10 items > from offset 0, and it increases to 220 ms to fetch 10 items from offset > 9900. > While I have 20000 records in the table, it takes 20 ms to fetch 10 items > from offset 0, 440 ms for 10 items from offset 9600 and 720 ms for 10 > items from offset 19950. > > My understanding is, since index table is created in the sorted order, > time to fetch from any offset should be the same. Why is the time to fetch > increasing when fetching from higher offset? Why is fetching time > increasing for the same offset when more records in the table? Is this > expected behavior from SQLite or is there something wrong with > schema/index/query?
When you do a "SELECT <something> FROM <somewhere> WHERE <conditions> OFFSET <x>" you are asking SQLite to run the base query and discard <x> result rows. So the time taken to perform the query is the pretty much the same as if you had just selected all the data and discarded it yourself (by not fetching the rows you did not want). That is: row=0 prepare(statement) while row < x: step(statement) row++ row=0 while row < y step(statement) ... retrieve and process row data ... finalize(statement) is the same thing as saying <statement> OFFSET x LIMIT y, just the database engine skips the rows for you. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
