[sqlite] Re: optimizing a query with ORDER BY and LIMIT

[Chris Eich]

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