Joe Wilson <[EMAIL PROTECTED]> wrote: > --- 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 loop order selection aside - the only question is which > EXPLAIN QUERY PLAN yields the fastest query for your dataset? > > Could you post the result of these queries? > > select count(*) from device; > select count(*) from device_perf_interval; > select count(distinct device_id) from device_perf_interval; > > I can see if the number of rows in device is small relative to dpi, > then it would favor (d, dpi). Otherwise it would favor (dpi, d) > since there are no constraints on device in your query and fewer > rows would be eliminated prior to the join. >
SQLite is suppose to do all of the above counting for you when you run the ANALYZE command. The results of ANALYZE are stored in the sqlite_stat1 table and are used by the query optimizer to construct better query plans. Have you run ANALYZE? If so, did it not help? Can you post the content of the sqlite_stat1 table? -- D. Richard Hipp <[EMAIL PROTECTED]> ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
