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] -----------------------------------------------------------------------------