[sqlite] Basic query slower with index?

[Seth Falcon]

Hi all,

I'm working with a table with ~3 million rows and 7 columns.
Recently, I found that a simple query takes longer _after_ adding an
index.  I find this surprising.  Can someone provide some insight?

Here are some details:

The schema:

    CREATE TABLE pmfeature (
        fid integer not null,
        strand integer,
        allele integer,
        fsetid integer not null references "featureSet" ("fsetid"),
        pos integer,
        x integer,
        y integer,
        UNIQUE("fid"));
    CREATE INDEX foo on pmfeature ("allele");

The query:

    select fid from pmfeature where allele = 0;

On my laptop, if I do (where basic.sql contains the above query):

    time sqlite thedb.sqlite < basic.sql > /dev/null

it takes about 9 sec without the 'foo' index and 13 sec with it.  The
query returns about half the records (1.5 million).

I tried using EXPLAIN, but I'm not yet experienced enough to know how
to interpret the results (other than I can see that there are many
more ops when an index is present).  Below is the explain output.

    sqlite> explain select fid from pmfeature where allele = 0;
    0|Goto|0|13|
    1|Integer|0|0|
    2|OpenRead|0|4|
    3|SetNumColumns|0|3|
    4|Rewind|0|11|
    5|Column|0|2|
    6|Integer|0|0|
    7|Ne|356|10|collseq(BINARY)
    8|Column|0|0|
    9|Callback|1|0|
    10|Next|0|5|
    11|Close|0|0|
    12|Halt|0|0|
    13|Transaction|0|0|
    14|VerifyCookie|0|10|
    15|Goto|0|1|
    16|Noop|0|0|

    sqlite> create index foo on pmfeature ("allele");
    
    sqlite> explain select fid from pmfeature where allele = 0;
    0|Goto|0|25|
    1|Integer|0|0|
    2|OpenRead|0|4|
    3|SetNumColumns|0|3|
    4|Integer|0|0|
    5|OpenRead|1|329850|keyinfo(1,BINARY)
    6|Integer|0|0|
    7|NotNull|-1|10|
    8|Pop|1|0|
    9|Goto|0|22|
    10|MakeRecord|1|0|d
    11|MemStore|0|0|
    12|MoveGe|1|22|
    13|MemLoad|0|0|
    14|IdxGE|1|22|+
    15|RowKey|1|0|
    16|IdxIsNull|1|21|
    17|IdxRowid|1|0|
    18|MoveGe|0|0|
    19|Column|0|0|
    20|Callback|1|0|
    21|Next|1|13|
    22|Close|0|0|
    23|Close|1|0|
    24|Halt|0|0|
    25|Transaction|0|0|
    26|VerifyCookie|0|11|
    27|Goto|0|1|
    28|Noop|0|0|

This is with sqlite 3.3.8 on OSX ppc.

Thanks,

+ seth

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