Max Vasilyev wrote: > http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor > and want to use WHERE, but what if 'title' is not unique?
If the ORDER BY columns are not unique, you cannot know which rows to display on which page. You must be able to uniquely identify rows. > - This is considered here: > http://stackoverflow.com/questions/21082956/sqlite-scrolling-cursor-how-to-scroll-correctly-with-duplicate-names > and yes, we can use title+rowid as lasttitle. But... it looks too complex > to be 'best practice' pattern. Feel free to propose something simpler that is still correct. > And is x100 slower than simple WHERE. > > SELECT * FROM "MainBooksView" > WHERE (Title = 'fff' AND Id > 101985) OR Title > 'fff' > ORDER BY Title > LIMIT 30 > Query time: 0.102s > I have index for the Title column. The latest SQLite supports the OR optimization for this query: EXPLAIN QUERY PLAN SELECT ...; 0|0|0|SCAN TABLE MainBooksView USING INDEX TitleIndex Run ANALYZE, and update your SQLite. Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
