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

> 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 >

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

> 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 ; > > What can I do to speed this up? I tried a third index on interval_end_date > but can't

[sqlite] optimizing a query with ORDER BY and LIMIT

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