I think the simplest approach for me is to get rid of the join on device. After I did so, SQLite uses the index on interval_end_date. I'll just need to grab the device -> device_type mapping and interpret it myself.
Chris On 7/11/07, Chris Eich <[EMAIL PROTECTED]> wrote:
Me again. I said at the end of the last thread that I had learned a lot. What I learned is that my query's performance problems were not due to picking a bad index (given my dataset, the two indices were identical for this query). Instead the problem seems to be caused by ORDER BY and LIMIT clauses, which I left out to "simplify" the question. :-( The real query is: 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 ORDER BY dpi.interval_end_date LIMIT <some number> ; What can I do to speed this up? I tried a third index on interval_end_date but can't get SQLite to notice it (at least in EXPLAIN QUERY PLAN output). Overview Of The Optimizer <http://sqlite.org/optoverview.html> (6.0 and 8.0) led me to think that a highly selective index would help here. Thanks again, Chris P.S. The relevant schema is: 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, ... ); 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); CREATE INDEX dpi2 ON device_perf_interval(interval_end_date); Dataset stats are: sqlite> select * from sqlite_stat1 where idx like 'dpi%' order by idx; device_perf_interval|dpi1|5256000 5256000 105120 105120 1 device_perf_interval|dpi2|5256000 5256000 105120 105120 105120 device_perf_interval|dpi3|5256000 50