Hi Clemens, 2015-01-08 13:34 GMT+03:00 Clemens Ladisch <clem...@ladisch.de>:
> > > 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. > That's why I think to rownum... Now I ended up with simulating index by a table and use it's PK as rownum. CREATE TABLE BookTitleIdx (title_rownum INTEGER PRIMARY KEY AUTOINCREMENT, BookID INTEGER NOT NULL); INSERT INTO BookTitleIdx SELECT NULL, rowid FROM Book ORDER BY Title Time: 0.369s Great! 126K records. SELECT Book.Id, Author.Name as Author, Book.Title, Genre.Name as Genre FROM BookTitleIdx INNER JOIN Book ON BookTitleIdx.BookID = Book.Id LEFT JOIN Author ON Book.AuthorID = Author.ID INNER JOIN Genre ON Book.GenreID = Genre.ID WHERE BookTitleIdx.title_rownum > 120000 ORDER BY BookTitleIdx.title_rownum LIMIT 30 Time: 0.001s and not surprising! As EXPLAIN QUERY PLAN looks excellent to me! 0 0 0 SEARCH TABLE BookTitleIdx USING INTEGER PRIMARY KEY (rowid>?) 0 1 1 SEARCH TABLE Book USING INTEGER PRIMARY KEY (rowid=?) 0 2 2 SEARCH TABLE Author USING INTEGER PRIMARY KEY (rowid=?) 0 3 3 SEARCH TABLE Genre USING INTEGER PRIMARY KEY (rowid=?) BTW, the query can be ...FROM Book INNER JOIN BookTitleIdx... no vice versa is required. But inner join order is critical. This query SELECT ... FROM Book LEFT JOIN Author ON Book.AuthorID = Author.ID INNER JOIN Genre ON Book.GenreID = Genre.ID INNER JOIN BookTitleIdx ON Book.Id = BookTitleIdx.BookID ... runs 0.5s and has Book table scan, etc... Just moving last join up makes the query plan as I want. So, for now I'm happy guys, thank you very much! 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. > > Updated and got 0.060s (x2 faster)! ANALYZE does not change anything. Thank you very much for EXPLAIN QUERY PLAN! It's much more readable than just EXPLAIN which i used but did not understand :) Cheers, Max _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
