On 1 Feb 2017, at 2:50pm, Anthrathodiyil, Sabeel (S.) <santh...@visteon.com> wrote:
> 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. Thanks for your schema listing, timings, and the thorough description of your problem. It saved a lot of time. Your INDEX and SELECT look fine, and the timings you give are plausible. I don’t think you’re doing anything wrong. When using an OFFSET clause SQLite has to do the following: 1) Find the first record in the index which fits the WHERE clause 2) Skip to the OFFSET number of rows after it If the index was arranged as one continuous fixed-width list, it would be easy to skip 19950 entries. But it’s not, it’s a binary tree. So SQLite has to iterate through those 19950 entries in tree form, and the time to do that is roughly proportional to the OFFSET number. Hope this helps. Does it look reasonable to you ? Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
