[sqlite] SQLite - SELECT query slowing down while fetching from higher offset.

[Anthrathodiyil, Sabeel (S.)]

Hi,
I am facing issue with SQLite performance when having more records in the table 
and fetching from higher offsets, though I have proper index in place. Using 
SQLite version 3.7.10 running on ARM Cortex A5 processor.

Here is my schema (relevant table and index)  and query

CREATE TABLE FileTable(
                FileID                                                     
INTEGER PRIMARY KEY,
                FileName                                             TEXT 
DEFAULT NULL,
                FileType                                                INTEGER 
DEFAULT 0,
                GenreID                                               INTEGER 
DEFAULT 0,
                ArtistID                                                 
INTEGER DEFAULT 0,
                ComposerID                                       INTEGER 
DEFAULT 0,
                AlbumID                                              INTEGER 
DEFAULT 0,
                TrackID                                                INTEGER 
DEFAULT 0,
                TrackName                                 TEXT DEFAULT NULL,
                TrackDuration                                   INTEGER DEFAULT 
0,
                InValidFlag                                         INTEGER 
DEFAULT 0
)
CREATE INDEX Idx_TrackName_OnFileTable ON FileTable(FileType, TrackName COLLATE 
NOCASE ASC);

SELECT FileID , TrackName, FileType, InValidFlag FROM  FileTable  WHERE 
FileType = 1  AND TrackName  <>""  ORDER BY TrackName  COLLATE NOCASE LIMIT 10 
OFFSET 19950

database connection opened with flags (SQLITE_OPEN_READWRITE | 
SQLITE_OPEN_FULLMUTEX)
PRAGMA journal_mode = MEMORY
PRAGMA synchronous = FULL
PRAGMA temp_store = 2
PRAGMA cache_size = 10000LL

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?

Best regards,
Sabeel
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users