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 USE TEMP B-TREE FOR ORDER BY
- Selen
>________________________________
> From: Dan Kennedy <[email protected]>
>To: [email protected]
>Sent: Wednesday, January 16, 2013 3:14 PM
>Subject: Re: [sqlite] Multi-column index is not used with IN operator
>
>On 01/16/2013 09:04 PM, Selen Schabenberger wrote:
>>
>>
>> The entry for the Tag index in sqlite_stat1:
>>
>> 460132 1289
>>
>> The results are returned in 163 ms on the network share.
>
>I can't reproduce that. Once I add the entry to sqlite_stat1
>it uses the IPK index. Can you post the new .dump of the schema
>and query again?
>
>Dan.
>
>
>
>>
>> - Selen
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
