> 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).
-- drop all other indexes on these tables
drop index dpi1;
drop index dpi2;
CREATE INDEX dpi5 on device_perf_interval(
interval_end_date,
interval_type,
interval_duration
);
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
ORDER BY dpi.interval_end_date
LIMIT 5;
0|1|TABLE device_perf_interval AS dpi WITH INDEX dpi5 ORDER BY
1|0|TABLE device AS d USING PRIMARY KEY
____________________________________________________________________________________
Take the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news,
photos & more.
http://mobile.yahoo.com/go?refer=1GNXIC
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
