--- 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.interval_type=1 AND dpi.interval_duration=300;
> 0|0|TABLE device AS d
> 1|1|TABLE device_perf_interval AS dpi WITH INDEX dpi2
> sqlite> explain query plan SELECT d.device_type, dpi.* FROM device d CROSS
> JOIN 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 dpi2
>
> Perhaps I'm misinterpreting this output; can someone elucidate for me?
Given the schema:
CREATE TABLE device (
device_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
device_type INTEGER NOT NULL
);
CREATE TABLE device_perf_interval (
interval_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
interval_type INTEGER DEFAULT 0, -- lookup in interval_type
device_id INTEGER NOT NULL,
interval_end_date INTEGER NOT NULL, -- time_t
interval_duration INTEGER NOT NULL, -- total duration (seconds)
stat_duration INTEGER NOT NULL, -- duration covered by stats
complete INTEGER DEFAULT 0,
exported INTEGER DEFAULT 0,
stat1 INTEGER DEFAULT 0,
stat2 INTEGER DEFAULT 0,
stat3 INTEGER DEFAULT 0,
stat4 INTEGER DEFAULT 0,
stat5 INTEGER DEFAULT 0,
stat6 INTEGER DEFAULT 0,
stat7 INTEGER DEFAULT 0,
stat8 INTEGER DEFAULT 0,
created_date INTEGER NOT NULL
);
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 get different results on sqlite 3.4.0 than you reported with 3.3.13.
Mind you, I do not have any data in these tables and did not run
ANALYZE.
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|1|TABLE device_perf_interval AS dpi WITH INDEX dpi2
1|0|TABLE device AS d USING PRIMARY KEY
sqlite> explain query plan SELECT d.device_type, dpi.* FROM device d
CROSS JOIN 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
Execute "drop table sqlite_stat1;" and re-run the explain query
plan commands and see what you get.
Also try running these commands. Note the '+' signs:
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|TABLE device AS d
1|1|TABLE device_perf_interval AS dpi WITH INDEX dpi1
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|1|TABLE device_perf_interval AS dpi WITH INDEX dpi2
1|0|TABLE device AS d USING PRIMARY KEY
____________________________________________________________________________________
Building a website is a piece of cake. Yahoo! Small Business gives you all the
tools to get online.
http://smallbusiness.yahoo.com/webhosting
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
