Doug Nebeker wrote:
So does that mean if I get "TABLE xyz" in the 'detail' column back and
it does NOT mention an index that a full table scan is taking place?
I found some info about EXPLAIN QUERY PLAN
(http://www.sqlite.org/cvstrac/wiki?p=QueryPlans) but haven't found out
exactly what the response columns mean.
Doug,
Yes, that's what that means. Here are some simple examples. Note it is
easier to see what is happening if you turn on the headers and go to
column mode.
SQLite version 3.3.5
Enter ".help" for instructions
sqlite> create table t(a integer primary key, b);
sqlite> .mode column
sqlite> .header on
sqlite> explain query plan select * from t;
order from detail
---------- ---------- ----------
0 0 TABLE t
sqlite> explain query plan select * from t where a > 100;
order from detail
---------- ---------- -------------------------
0 0 TABLE t USING PRIMARY KEY
sqlite> explain query plan select * from t where b > 100;
order from detail
---------- ---------- ----------
0 0 TABLE t
sqlite> create index t_b on t(b);
sqlite> explain query plan select * from t where b > 100;
order from detail
---------- ---------- ----------------------
0 0 TABLE t WITH INDEX t_b
sqlite> create table t2(b, c);
sqlite> explain query plan select * from t left join t2 using(b)
where c > 100;
order from detail
---------- ---------- ----------
0 0 TABLE t
1 1 TABLE t2
sqlite> explain query plan select * from t left join t2 using(b)
where a > 100;
order from detail
---------- ---------- -------------------------
0 0 TABLE t USING PRIMARY KEY
1 1 TABLE t2
sqlite>sqlite>
The first query does a table scan,and it has to because it needs to
return all records. The second uses the primary key index. The third
does a table scan because there is no index on column b. Finally the
fourth uses the new index on column b created just before. The last two
show how multiple table scans are listed for joins.
I'm not sure what the order and from columns are myself. Perhaps someone
else can explain them.
HTH
Dennis Cote
