I realize that but thought that device_id would be included because of the d.device_id=dpi.device_id term. What am I missing?
Chris On 7/10/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> 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: This query cannot use an index on (interval_type, device_id). So only an index on interval_type can be used. For that, dpi1 and dpi2 are equally suitable. SQLite just picks one of the two. Realize that an index cannot skip fields. An index on (A, B, C) is not useful when the query only filters on A and C. Or rather, it can be used to quickly satisfy a condition on A, but not on C. On the other hand, such an index can be used to quickly satisfy a condition on A and B, or A and B and C. Igor Tandetnik ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
