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 <danielk1...@gmail.com> >To: sqlite-users@sqlite.org >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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users