[sqlite] "wrong" index chosen, why?

[Chris Eich]

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 (
   interval_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
   interval_type INTEGER DEFAULT 0,        -- lookup in interval_type
   device_id INTEGER NOT NULL,
   interval_end_date INTEGER NOT NULL,     -- time_t
   interval_duration INTEGER NOT NULL,     -- total duration (seconds)
   stat_duration INTEGER NOT NULL,         -- duration covered by stats
   complete INTEGER DEFAULT 0,
   exported INTEGER DEFAULT 0,
   stat1 INTEGER DEFAULT 0,
   stat2 INTEGER DEFAULT 0,
   stat3 INTEGER DEFAULT 0,
   stat4 INTEGER DEFAULT 0,
   stat5 INTEGER DEFAULT 0,
   stat6 INTEGER DEFAULT 0,
   stat7 INTEGER DEFAULT 0,
   stat8 INTEGER DEFAULT 0,
   created_date INTEGER NOT NULL
);

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:

sqlite> explain query plan 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;
0|0|TABLE device AS d
1|1|TABLE device_perf_interval AS dpi WITH INDEX dpi2

These rows exist in sqlite_stat1:

INSERT INTO "sqlite_stat1" VALUES('device_perf_interval','dpi2','43200 43200
864 864 864');
INSERT INTO "sqlite_stat1" VALUES('device_perf_interval','dpi1','43200 43200
864 864 1');

Any ideas?

Thanks,

Chris