>From what I understand, SQLite performs query planning based on cost estimates.
A full table scan costs less per record than an index lookup.
1) (scan cost) * (IN Lookup)
is compared with
2) (IN size) * (key lookup) * (sort factor)
IN Lookup cost is logarithmic; IN size is linear. The number 22 appears to be
where a plot of the costs cross over.
ANALYZE will improve SQLite's cost estimates.
Gunter
-----Ursprüngliche Nachricht-----
Von: Selen Schabenberger [mailto:[email protected]]
Gesendet: Donnerstag, 13. Dezember 2012 16:07
An: [email protected]
Betreff: [sqlite] Multi-column index is not used with IN operator
Hi All,
I am observing some strange behaviour on my database when I execute a query
with an IN operator having more than "22" expressions. My table structure looks
basically as follows:
CREATE TABLE "Messages" ("Id" INTEGER PRIMARY KEY NOT NULL, "Tag" INTEGER NOT
NULL, "Flag" INTEGER )
I have a multi-column index on (Tag, Flag, Id) as well as a single column
index on the Flag column.
If I execute the following query on this table, the Messages table is scanned
using the primary key and the results are returned in 20 seconds.
> explain query plan Select Messages.Id from Messages 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 Messages.Id limit
> 0, 100
>> Scan table Messages using integer primary key
>> Execute list subquery1
However if I reduce the number of expressions in the IN operator, the index on
the (Tag, Flag, Id) is used.
> explain query plan Select Messages.MessageId from Messages where (AspTag in (
> 1146883, 1146888, 1146892, 1146894, 1146896, 1146898, 1146920, 1146922,
> 1147912, 1147914, 1147968, 1147970, 1147976, 1147978, 1148012, 1148015,
> 1148016, 1148018, 1148020, 1148022, 1148040, 1148042) and PduFlag=1) order
> by MessageId limit 0, 100
>> Search table Messages using covering index IDX_TAG_FLAG_ID
>> Execute list subquery1
>> Use temp b-tree for order by
If I rewrite the first query in multiple IN operators connecting with OR then I
can again use my multi-column index and it is much more efficient than the full
table scan.
> explain query plan Select Messages.Id from Messages where (Tag in (
> 1148138,1148191, 1148232, 1148234, 1167643, 1167659,1146883, 1146884,
> 1146886, 1146888, 1146892, 1146894, 1146896, 1146898, 1146920, 1146922,
> 1147912, 1147914, 1147968, 1147970, 1147976, 1147978 ) or Tag in (1148012,
> 1148015, 1148016, 1148018, 1148020, 1148022, 1148040, 1148042, 1148079,
> 1148136, 1167660, 1167663, 1167667, 1167671, 1167675) ) and Flag=1 order by
> Messages.Id limit 0, 100
>> Execute list subquery1
>> Search table Messages using covering index IDX_TAG_FLAG_ID
>> Execute list subquery1
>> Search table Messages using covering index IDX_TAG_FLAG_ID
>> Use temp b-tree for order by
Does anybody have any idea why it behaves so? Are there any restrictions with
the IN operator?
Regards,
-Selen
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--------------------------------------------------------------------------
Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: [email protected]
This e-mail is confidential and may well also be legally privileged. If you
have received it in error, you are on notice as to its status and accordingly
please notify us immediately by reply e-mail and then delete this message from
your system. Please do not copy it or use it for any purposes, or disclose its
contents to any person as to do so could be a breach of confidence. Thank you
for your cooperation.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
