Re: [sqlite] Re: Re: "wrong" index chosen, why?

--- Chris Eich <[EMAIL PROTECTED]> wrote: > Thanks, I tried both 3.3.13 and 3.4.0. With 3.3.13 the +'s didn't make a > difference; the loops always were in d, dpi order with index dpi1 used. With > 3.4.0 the loops were always in dpi, d order with index dpi1 used, unless I > used CROSS JOIN. Index

Re: [sqlite] Re: Re: "wrong" index chosen, why?

Thanks, I tried both 3.3.13 and 3.4.0. With 3.3.13 the +'s didn't make a difference; the loops always were in d, dpi order with index dpi1 used. With 3.4.0 the loops were always in dpi, d order with index dpi1 used, unless I used CROSS JOIN. But the basic question I have is why (given d, dpi orde

Re: [sqlite] Re: Re: "wrong" index chosen, why?

--- Chris Eich <[EMAIL PROTECTED]> wrote: > The output of EXPLAIN QUERY PLAN doesn't change when I use the CROSS JOIN > trick to disable table reordering: > > sqlite> explain query plan SELECT d.device_type, dpi.* FROM device d, > device_perf_interval dpi WHERE d.device_id=dpi.device_id AND > dpi.

Re: [sqlite] Re: Re: "wrong" index chosen, why?

The output of EXPLAIN QUERY PLAN doesn't change when I use the CROSS JOIN trick to disable table reordering: sqlite> 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; 0|0|TA

[sqlite] Re: Re: "wrong" index chosen, why?

Chris Eich <[EMAIL PROTECTED]> wrote: Overview Of The Optimizer (1.0 says "column = expression" not "constant expression", 5.0 says "joins are nested loops") and the EXPLAIN QUERY PLAN output gave me this mental model: for (d in device) for (dpi in index(dpi

[sqlite] Re: Re: "wrong" index chosen, why?

Chris Eich <[EMAIL PROTECTED]> wrote: 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? The query selects _all_ values of device_id from device_perf_interval, and for each tries to find a matching record in device. How