BETWEEN doesn't use an index either. I have yet to get an explaination for either of them :(
On 8/2/05, Martijn Voncken <[EMAIL PROTECTED]> wrote: > Hi, > > I'm using pysqlite2,compiled against sqlite 3.2.1 > I thought that IN uses an index. > > Quote from : > http://www.mail-archive.com/sqlite-users%40sqlite.org/msg09004.html > >Indices are not currently used unless you say: > > SELECT * FROM a WHERE f1 IN (5,11); > > But "IN" does not use an index in my query. > perhaps because there are 2 AND statements before the IN? > > Full table scan using IN (0.22 sec) > select file_attr.fileid from file_attr /*fileid,*/ > where file_attr.mediaid=1 /*audio*/ and file_attr.attrid = 16 and > file_attr.value in ('wontfindthis1','test-not-found') /*artist*/ > > compare: Fast query using = (0.0064 sec): > select file_attr.fileid from file_attr /*fileid,*/ > where file_attr.mediaid=1 /*audio*/ and file_attr.attrid = 16 and > file_attr.value = 'test-not-found' /*artist*/ > > compare : Full table scan using LIKE (0.26 sec): > select file_attr.fileid from file_attr /*fileid,*/ > where file_attr.mediaid=1 /*audio*/ and file_attr.attrid = 16 and > file_attr.value like '%test-not-found%' > ----- > Schema: > CREATE TABLE file_attr (fileid integer,mediaid integer,attrid > integer,value unicode); > integer); > CREATE UNIQUE INDEX ix_file_attr on file_attr (fileid,attrid); > CREATE INDEX ix_file_attr_media_val on file_attr (mediaid,attrid,value); > > > Martijn. > p.s. > Thanks for sqlite,it's really fast and easy to setup. > -- Cory Nelson http://www.int64.org
