On 01/17/2013 06:32 PM, Selen Schabenberger wrote:
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.
Looks like with stat3 enabled the estimate of the number of rows matched
by the WHERE clause is better. So it uses INDEX_IDX_TAG. But not the
composite index as the stat3 estimation is disabled if the query uses
more than the first column of the index (in this case it uses two -
'Tag' and 'Flag'). That's probably something that could be enhanced at
some point.
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?
You could figure out why the stat1 data is deceptive in this case and
whether there is anything that can be done about it.
Or add an INDEXED BY clause to the query.
Adding a unary '+' operator in front of 'Id' might work too.
Dan.
- 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 &n bsp; USE TEMP B-TREE FOR ORDER BY
>________________________________
> From: Dan Kennedy <danielk1...@gmail.com
<mailto:danielk1...@gmail.com>>
>To: General Discussion of SQLite Database <sqlite-users@sqlite.org
<mailto: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 <mailto:sqlite-users@sqlite.org>
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org <mailto: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
