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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
