Zbigniew Baniewski <[EMAIL PROTECTED]> wrote: > On Tue, Dec 18, 2007 at 12:24:25PM +0000, Simon Davies wrote: > > > Prob shd be http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor > > One question - using the example mentioned there: > > If we've created an index: CREATE INDEX example1 ON tracks(singer, title); > > So, it'll make the query, like below, much faster: > > SELECT title FROM tracks > WHERE singer='Madonna' > AND title<:firsttitle > ORDER BY title DESC > LIMIT 5; > > ....but I understand, that when I'll try to add in the query a field not > covered by index "example1", like this: > > SELECT title FROM tracks > WHERE singer='Madonna' > AND title<:firsttitle > AND year_ed > 1985; > > ....so, then I'm losing every profit from having "example1" index, right? Or > perhaps "not quite every", and some speedup still remains - just because > _some_ columns are covered by example1 anyway?
The index is still used to speed the search. But the extra "AND year_ed>1985" term requires SQLite to check each row coming out of the index an discard those for which the condition is not true. This might be a small or a large loss in performance, depending on how many rows match the condition. -- D. Richard Hipp <[EMAIL PROTECTED]> ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
