[sqlite] Re: :Using sqlite3_progress_handler for GUI application

[Igor Tandetnik]

Zbigniew Baniewski <zb-pij/[EMAIL PROTECTED]> wrote:
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?

No. SQLite can still use the index to satisfy the first two conditions. 
Then it will scan all the remaining records (but not the whole table) to 
satisfy the last condition. The queries with and without the last 
condition (one not convered by index) would run in approximately the 
same time.

Igor Tandetnik 


-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------