-----Urspr?ngliche Nachricht-----
Von: Eric Hill [mailto:Eric.Hill at jmp.com]
Gesendet: Dienstag, 19. Mai 2015 22:44
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Virtual Table query - why isn't SQLite using my indexes?
Hey, Gunter,
...
But then what about a query like this:
SELECT * FROM T1
LEFT OUTER JOIN T2 ON ( T2.a = T1.a ) AND ( T2.b = T1.b ) AND (
T2.c = T1.c );
xBestIndex will get called here for T1 with 3 constraints, c, b, and a, in that
order. In this case, though, it seems (to the uninitiated, at least ;-) that
the "best index" would be:
CREATE INDEX T1_all ON T1 (c, b, a);
(or would it?) but the xBestIndex call does not contain any information that
tells me that. To be able to know that, I would have to know independently
what the join conditions are. And I could make that knowledge available to my
xBestIndex implementation. But how much better would that be than an index on
c and partial scans to find the others?
(I tried this exact example, once creating an index on just T1.c and once
creating an index on T1 (c, b, a), and in both cases, my index was not used by
SQLite. So I don't think I understand what SQLite wants.)
Eric,
an Index on the LHS Table of a LEFT OUTER join never helps, because you are
requesting to retrieve all rows of the LHS anyway. SQLite is calling xBestIndex
with constraints on T1 for symmetry purposes only (this may even be considered
a bug). Try again with indexes on T2.
The more xBestIndex knows about a table, the better its answers to SQLite
will be. Unfortunately, xBestIndex has no way of knowing how many joins the
passed constraints are coming from (apart from the obvious giveaway of repeated
fields).
If you know nothing about a table except for the names of the fields and the
number of rows, then you are best off choosing the first constraint only. (rows
= cost = log n)
If you know the cardinality of each field, you should choose the one with the
highest cardinality c. (rows = n/c, cost = rows + log n)
If you at least know which sets of fields are guaranteed to be unique, choose
the ?most fulfilled? set (tie break: least number of fields, highest
cardinality).
Gunter
___________________________________________
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at
This communication (including any attachments) is intended for the use of the
intended recipient(s) only and may contain information that is confidential,
privileged or legally protected. Any unauthorized use or dissemination of this
communication is strictly prohibited. If you have received this communication
in error, please immediately notify the sender by return e-mail message and
delete all copies of the original communication. Thank you for your cooperation.
