Chris Eich <[EMAIL PROTECTED]> wrote:
I have a database with the following tables:
CREATE TABLE device (
device_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
device_type INTEGER NOT NULL, -- lookup in device_type
...
);
CREATE TABLE device_perf_interval (
[snip]
);
and two indexes on the latter table:
CREATE INDEX dpi1 ON device_perf_interval(
interval_type,
device_id,
interval_duration,
interval_end_date
);
CREATE INDEX dpi2 ON device_perf_interval(
interval_type,
device_id,
complete,
exported
);
I expect that dpi1 will be used for this query:
SELECT d.device_type, dpi.* FROM device d, device_perf_interval dpi
WHERE d.device_id=dpi.device_id AND dpi.interval_type=1 AND
dpi.interval_duration=300;
When I ask SQLite (3.3.13) for its query plan, though, it indicates
dpi2 will be used:
This query cannot use an index on (interval_type, device_id). So only an
index on interval_type can be used. For that, dpi1 and dpi2 are equally
suitable. SQLite just picks one of the two.
Realize that an index cannot skip fields. An index on (A, B, C) is not
useful when the query only filters on A and C. Or rather, it can be used
to quickly satisfy a condition on A, but not on C. On the other hand,
such an index can be used to quickly satisfy a condition on A and B, or
A and B and C.
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
