I am currently working on a project that requires retrieving a list of all the rows from a FTS3 table. The ordering of the results varies by search criteria. Since this is for an embedded project the list results are passed in chunks to another module to give the appearance of faster operations.
Currently the selection of rows is done as follows: SELECT songid, title, artist FROM SongTable ORDER BY title COLLATE NOCASE DESC, artist COLLATE NOCASE DESC, songid DESC LIMIT 2000 OFFSET 4000; With the offset increasing for each subsequent page of data. The big problem I have with this is that when the offset gets large there is a huge inefficiency because all rows are selected and then stepped through until the offset is reached. My current test database has 34,000 rows. I am considering changing to queries of the following style but am unsure of the performance gains since I will still not be using the MATCH operator on the data. SELECT songid, title, artist FROM songtable WHERE (title = "some title AND artist = "some artist" AND songid > 4194419) OR (title = "some title" AND artist > "some artist") OR title > "some title" ORDER BY title, artist, songid LIMIT 2000; When using the "explain query plan" operator I see that both of the above cases only use "VIRTUAL TABLE INDEX 0" The advantage of the second case is that is only selects the necessary rows, however it does use need to perform comparisons and I am unsure what the performance hit of the comparison operators on a FTS3 table is. sqlite> explain query plan SELECT songid, title, artist FROM songtable WHERE (title = "some title AND artist = "some artist" AND songid > 4194419) OR (title = "some title" AND artist > "some artist") OR title > "some title" ORDER BY title, artist, songid LIMIT 2000; 0|0|0|SCAN TABLE songtable VIRTUAL TABLE INDEX 0: (~0 rows) 0|0|0|SCAN TABLE songtable VIRTUAL TABLE INDEX 0: (~0 rows) 0|0|0|SCAN TABLE songtable VIRTUAL TABLE INDEX 0: (~0 rows) 0|0|0|USE TEMP B-TREE FOR ORDER BY sqlite> explain query plan SELECT songid, title, artist FROM SongTable ORDER BY title COLLATE NOCASE DESC, artist COLLATE NOCASE DESC, songid DESC LIMIT 2000 OFFSET 4000; 0|0|0|SCAN TABLE songtable VIRTUAL TABLE INDEX 0: (~0 rows) 0|0|0|USE TEMP B-TREE FOR ORDER BY Can someone enlighten me about this. To me a lot of the details appear to be hidden since my main SongTable is a FTS3 virtual table. I hope this makes sense. Thanks, Travis _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
