Whoops, you're right my example won't use the index: SQLite version 3.5.6 Enter ".help" for instructions sqlite> CREATE TABLE tracks (id INTEGER PRIMARY KEY, title TEXT); sqlite> CREATE INDEX tracksIndex ON tracks (title DESC, id ASC); sqlite> EXPLAIN QUERY PLAN SELECT * FROM tracks WHERE title<:firsttitle OR (title=:firsttitle AND id>:firstrow) ORDER BY title DESC, id ASC; 0|0|TABLE tracks sqlite> EXPLAIN QUERY PLAN SELECT * FROM tracks WHERE title<=:firsttitle AND (title!=:firsttitle OR rowid<:firstrowid) ORDER BY title DESC, id ASC; 0|0|TABLE tracks WITH INDEX tracksIndex
-Jeff On 3/15/08, Tomas Lee <[EMAIL PROTECTED]> wrote: > On 2008 March 15 (Sat) 05:21:53pm PDT, Jeff Hamilton <[EMAIL PROTECTED]> > wrote: > > What about something like this: > > > > SELECT title FROM tracks > > WHERE singer='Madonna' > > AND (title<:firsttitle OR (title=:firsttitle AND rowid<:firstrowid)) > > ORDER BY title DESC, rowid ASC > > LIMIT 5; > > > > Then you only have to remember the single title and rowid of the first > > item in the list. You'd have to add the rowid ASC to your index as > > well, but the index already needs to store the rowid so I don't think > > it would take more space. > > > That's a clever idea. But is SQLite's query optimizer smart enough to > know it can use the index on title for that query? If you re-write it > to be > > > SELECT title FROM tracks > WHERE singer='Madonna' > > AND title<=:firsttitle AND (title!=:firsttitle OR rowid<:firstrowid) > > ORDER BY title DESC, rowid ASC > LIMIT 5; > > > then it surely should realize that it can use the index on title. > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
