[sqlite] index is not used for IN but it is used for =

[Martijn Voncken]

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.