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