Dan, I have just realised that with the sqlite_stat3 table, the query optimizer
uses the INDEX IDX_TAG. When I drop this table, the PK is used as you said.
But the concatenated index is still not used with the stat3.
Knowing that the query could be 60 times faster, is there anything I can do to
speed up this query or you think it is a bug in the optimizer?
- Selen
>________________________________
> From: Selen Schabenberger <selen_oz...@yahoo.com>
>To: General Discussion of SQLite Database <sqlite-users@sqlite.org>; Dan
>Kennedy <danielk1...@gmail.com>
>Sent: Wednesday, January 16, 2013 3:46 PM
>Subject: Re: [sqlite] Multi-column index is not used with IN operator
>
>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
>
>
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
