Joe, the numbers are 100, 4236301 and 100 respectively (as you suspected,
there are few devices, many intervals, and all devices have intervals).
Richard, sqlite_stat1 has these rows for the dpi indexes:
INSERT INTO "sqlite_stat1" VALUES('device_perf_interval','dpi1','4236301
2118151 21182 21182 1');
INSERT INTO "sqlite_stat1" VALUES('device_perf_interval','dpi2','4236301
2118151 21182 14122 14122');
Before ANALYZE, SQLite wanted to run the query as (dpi, d). After, it runs
as (d, dpi) and does use three columns from dpi1 (thanks Igor!):
sqlite> explain query plan SELECT count(*) 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|TABLE device AS d
1|1|TABLE device_perf_interval AS dpi WITH INDEX dpi1
sqlite> .explain on
sqlite> explain SELECT count(*) FROM device d, device_perf_interval dpi
WHERE d.device_id=dpi.device_id AND dpi.interval_type=1 AND
dpi.interval_duration=300;
addr opcode p1 p2
p3
---- -------------- ---------- ----------
---------------------------------
0 MemNull 0
0
1 Goto 0
29
2 Integer 0
0
3 OpenRead 0
2
4 SetNumColumns 0
0
5 Integer 0
0
6 OpenRead 2 37
keyinfo(4,BINARY,BINARY)
7 SetNumColumns 2
5
8 Rewind 0
23
9 Integer 1
0
10 IsNull -1
22
11 Rowid 0
0
12 IsNull -2
22
13 Integer 300
0
14 IsNull -3
22
15 MakeRecord 3 0
dddd
16 MemStore 1
0
17 MoveGe 2
22
18 MemLoad 1
0
19 IdxGE 2 22
+
20 AggStep 0 0
count(0)
21 Next 2
18
22 Next 0
9
23 Close 0
0
24 Close 2
0
25 AggFinal 0 0
count(0)
26 MemLoad 0
0
27 Callback 1
0
28 Halt 0
0
29 Transaction 0
0
30 VerifyCookie 0
43
31 Goto 0
2
32 Noop 0
0
Chris
On 7/11/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
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]
-----------------------------------------------------------------------------
