Sorry, wrong query wrong result.
But still when I add the order by, the index is used:
Select * from Message where ((Tag in ( 1146883, 1146884, 1146886, 1146888,
1146892, 1146894, 1146896, 1146898, 1146920, 1146922, 1147912, 1147914,
1147968, 1147970, 1147976, 1147978, 1148012, 1148015, 1148016, 1148018,
1148020, 1148022, 1148040, 1148042, 1148079, 1148136, 1148138, 1148191,
1148232, 1148234, 1167643, 1167659, 1167660, 1167663, 1167667, 1167671, 1167675
) ) and Flag=1) order by Id limit 200
selectId order from detail
0 0 0 SEARCH TABLE Message USING INDEX IDX_TAG
(Tag=?) (~33 rows)
0 0 0 EXECUTE LIST SUBQUERY 1
0 0 0 USE TEMP B-TREE FOR ORDER BY
>________________________________
> From: Dan Kennedy <danielk1...@gmail.com>
>To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
>Sent: Wednesday, January 16, 2013 3:40 PM
>Subject: Re: [sqlite] Multi-column index is not used with IN operator
>
>On 01/16/2013 09:22 PM, Selen Schabenberger wrote:
>> PRAGMA foreign_keys=OFF;
>> BEGIN TRANSACTION;
>> CREATE TABLE 'Message' ('Id' INTEGER PRIMARY KEY NOT NULL, 'Tag' INTEGER
>> NOT NULL, 'Flag' INTEGER NOT NULL );
>> ANALYZE sqlite_master;
>> INSERT INTO "sqlite_stat1" VALUES('Message','IDX_TAG','460132 1289');
>> INSERT INTO "sqlite_stat1"
>> VALUES('Message','IDX_MSGS_TAG_FLAG_ID','460132 1289 1275 1');
>> CREATE INDEX 'IDX_MSGS_TAG_FLAG_ID' on 'Message' ('Tag', 'Flag', 'Id');
>> CREATE INDEX IDX_TAG on Message (Tag);
>> COMMIT;
>>
>> Select * from Message where ((Tag in ( 1146883, 1146884, 1146886,
>> 1146888, 1146892, 1146894, 1146896, 1146898, 1146920, 1146922, 1147912,
>> 1147914, 1147968, 1147970, 1147976, 1147978, 1148012, 1148015, 1148016,
>> 1148018, 1148020, 1148022, 1148040, 1148042, 1148079, 1148136, 1148138,
>> 1148191, 1148232, 1148234, 1167643, 1167659, 1167660, 1167663, 1167667,
>> 1167671, 1167675 ) ) and Flag=1) limit 200
>>
>> selectId order from detail
>> 0 0 0 SEARCH TABLE Message USING INDEX IDX_TAG (Tag=?) (~432 rows)
>> 0 0 0 EXECUTE LIST SUBQUERY 1
>> 0 0 0 & nbsp; USE TEMP B-TREE FOR ORDER BY
>
>The query and the result above don't match.
>
>If I add the ORDER BY clause to the query it uses the IPK index.
>
>Dan.
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
