*** DRAFT ***

SQLite Requirement Matrix Details
eqp.html

Index Summary Markup Original

R-28902-17274-01624-42364-46386-62149-20745-43372 tcl slt th3 src

EXPLAIN QUERY PLAN is most useful on a SELECT statement, but may also appear with other statements that read data from database tables (e.g. UPDATE, DELETE, INSERT INTO ... SELECT).

/* IMP: R-28902-17274 */

# EVIDENCE-OF: R-28902-17274 EXPLAIN QUERY PLAN is most useful on a
# SELECT statement, but may also appear with other statements that read
# data from database tables (e.g. UPDATE, DELETE, INSERT INTO ...
# SELECT).

R-43064-28843-60545-11344-25729-47633-22876-03755 tcl slt th3 src

For each table read by the query, the output of EXPLAIN QUERY PLAN includes a record for which the value in the "detail" column begins with either "SCAN" or "SEARCH".

/* IMP: R-43064-28843 */

# EVIDENCE-OF: R-43064-28843 For each table read by the query, the
# output of EXPLAIN QUERY PLAN includes a record for which the value in
# the "detail" column begins with either "SCAN" or "SEARCH".

R-54961-28617-11438-13073-24028-16766-12231-36247 tcl slt th3 src

"SCAN" is used for a full-table scan, including cases where SQLite iterates through all records in a table in an order defined by an index.

/* IMP: R-54961-28617 */

# EVIDENCE-OF: R-54961-28617 "SCAN" is used for a full-table scan,
# including cases where SQLite iterates through all records in a table
# in an order defined by an index.

R-38242-53217-63508-42050-45799-27349-00700-51305 tcl slt th3 src

"SEARCH" indicates that only a subset of the table rows are visited.

/* IMP: R-38242-53217 */

# EVIDENCE-OF: R-38242-53217 "SEARCH" indicates that only a subset of
# the table rows are visited.

R-42029-48346-24855-30902-19718-06307-39903-13955 tcl slt th3 src

Each SCAN or SEARCH record includes the following information:

The name of the table, view, or subquery that data is read from.
Whether or not an index or automatic index is used.
Whether or not the covering index optimization applies.
Which terms of the WHERE clause are used for indexing.

/* IMP: R-42029-48346 */

# EVIDENCE-OF: R-42029-48346 Each SCAN or SEARCH record includes the
# following information: The name of the table, view, or subquery that
# data is read from. Whether or not an index or automatic index is used.
# Whether or not the covering index optimization applies. Which terms of
# the WHERE clause are used for indexing.

R-23667-57149-13692-40760-52077-20986-45375-02136 tcl slt th3 src

sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
QUERY PLAN
`--SCAN t1

/* IMP: R-23667-57149 */

# EVIDENCE-OF: R-23667-57149 sqlite> EXPLAIN QUERY PLAN SELECT a, b
# FROM t1 WHERE a=1; QUERY PLAN `--SCAN t1

R-16170-02914-14290-07150-03100-60317-35403-36262 tcl slt th3 src

sqlite> CREATE INDEX i1 ON t1(a);
sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
QUERY PLAN
`--SEARCH t1 USING INDEX i1 (a=?)

/* IMP: R-16170-02914 */

# EVIDENCE-OF: R-16170-02914 sqlite> CREATE INDEX i1 ON t1(a);
# sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; QUERY
# PLAN `--SEARCH t1 USING INDEX i1 (a=?)

R-31214-22204-30542-52251-23834-20094-62182-37235 tcl slt th3 src

sqlite> CREATE INDEX i2 ON t1(a, b);
sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; 
QUERY PLAN
`--SEARCH t1 USING COVERING INDEX i2 (a=?)

/* IMP: R-31214-22204 */

# EVIDENCE-OF: R-31214-22204 sqlite> CREATE INDEX i2 ON t1(a, b);
# sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; QUERY
# PLAN `--SEARCH t1 USING COVERING INDEX i2 (a=?)

R-57880-35169-30628-02848-50703-50828-58750-28510 tcl slt th3 src

sqlite> EXPLAIN QUERY PLAN SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2;
QUERY PLAN
|--SEARCH t1 USING INDEX i2 (a=? AND b>?)
`--SCAN t2

/* IMP: R-57880-35169 */

# EVIDENCE-OF: R-57880-35169 sqlite> EXPLAIN QUERY PLAN SELECT t1.*,
# t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2; QUERY PLAN |--SEARCH t1
# USING INDEX i2 (a=? AND b>?) `--SCAN t2

R-12621-54688-01749-38007-62215-41521-15622-18085 tcl slt th3 src

The order of the entries indicates the nesting order.

/* IMP: R-12621-54688 */

# EVIDENCE-OF: R-12621-54688 The order of the entries indicates the
# nesting order.

R-01569-01564-08645-29195-59730-50441-26159-21102 tcl slt th3 src

sqlite> EXPLAIN QUERY PLAN SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2;
QUERY PLAN
|--SEARCH t1 USING INDEX i2 (a=? AND b>?)
`--SCAN t2

/* IMP: R-01569-01564 */

# EVIDENCE-OF: R-01569-01564 sqlite> EXPLAIN QUERY PLAN SELECT t1.*,
# t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2; QUERY PLAN |--SEARCH t1
# USING INDEX i2 (a=? AND b>?) `--SCAN t2

R-06511-19994-39421-53412-16339-26783-08670-41596 tcl slt th3 src

sqlite> CREATE INDEX i3 ON t1(b);
sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2;
QUERY PLAN
`--MULTI-INDEX OR
   |--SEARCH t1 USING COVERING INDEX i2 (a=?)
   `--SEARCH t1 USING INDEX i3 (b=?)

/* IMP: R-06511-19994 */

# EVIDENCE-OF: R-06511-19994 sqlite> CREATE INDEX i3 ON t1(b);
# sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2; QUERY
# PLAN `--MULTI-INDEX OR |--SEARCH t1 USING COVERING INDEX i2 (a=?)
# `--SEARCH t1 USING INDEX i3 (b=?)

R-00068-42258-60542-46185-56559-40653-31263-18427 tcl slt th3 src

sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c;
QUERY PLAN
|--SCAN t2
`--USE TEMP B-TREE FOR ORDER BY

/* IMP: R-00068-42258 */

# EVIDENCE-OF: R-00068-42258 sqlite> EXPLAIN QUERY PLAN SELECT c, d
# FROM t2 ORDER BY c; QUERY PLAN |--SCAN t2 `--USE TEMP B-TREE FOR ORDER
# BY

R-28198-38283-36055-63639-34430-05979-02434-46737 tcl slt th3 src

sqlite> CREATE INDEX i4 ON t2(c);
sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c; 
QUERY PLAN
`--SCAN t2 USING INDEX i4

/* IMP: R-28198-38283 */

# EVIDENCE-OF: R-28198-38283 sqlite> CREATE INDEX i4 ON t2(c);
# sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c; QUERY
# PLAN `--SCAN t2 USING INDEX i4

R-40094-47392-07559-50532-14131-32118-23991-53984 tcl slt th3 src

sqlite> EXPLAIN QUERY PLAN SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2;
|--SCAN TABLE t2 USING COVERING INDEX i4
|--SCALAR SUBQUERY
|  `--SEARCH t1 USING COVERING INDEX i2 (a=?)
`--CORRELATED SCALAR SUBQUERY
   `--SEARCH t1 USING INDEX i3 (b=?)

/* IMP: R-40094-47392 */

# EVIDENCE-OF: R-40094-47392 sqlite> EXPLAIN QUERY PLAN SELECT
# (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2;
# |--SCAN TABLE t2 USING COVERING INDEX i4 |--SCALAR SUBQUERY |
# `--SEARCH t1 USING COVERING INDEX i2 (a=?) `--CORRELATED SCALAR
# SUBQUERY `--SEARCH t1 USING INDEX i3 (b=?)

R-40969-30202-57015-62747-35327-16253-50512-36730 tcl slt th3 src

Unless the flattening optimization is applied, if a subquery appears in the FROM clause of a SELECT statement, SQLite can either run the subquery and stores the results in a temporary table, or it can run the subquery as a co-routine. The following query is an example of the latter. The subquery is run by a co-routine. The outer query blocks whenever it needs another row of input from the subquery. Control switches to the co-routine which produces the desired output row, then control switches back to the main routine which continues processing. ^(

sqlite> EXPLAIN QUERY PLAN SELECT count(*)
      > FROM (SELECT max(b) AS x FROM t1 GROUP BY a) AS qqq
      > GROUP BY x;
QUERY PLAN
|--CO-ROUTINE qqq
|  `--SCAN t1 USING COVERING INDEX i2
|--SCAN qqqq
`--USE TEMP B-TREE FOR GROUP BY

/* IMP: R-40969-30202 */

# EVIDENCE-OF: R-40969-30202 Unless the flattening optimization is
# applied, if a subquery appears in the FROM clause of a SELECT
# statement, SQLite can either run the subquery and stores the results
# in a temporary table, or it can run the subquery as a co-routine. The
# following query is an example of the latter. The subquery is run by a
# co-routine. The outer query blocks whenever it needs another row of
# input from the subquery. Control switches to the co-routine which
# produces the desired output row, then control switches back to the
# main routine which continues processing. ^(sqlite> EXPLAIN QUERY
# PLAN SELECT count(*) > FROM (SELECT max(b) AS x FROM t1 GROUP BY a)
# AS qqq > GROUP BY x; QUERY PLAN |--CO-ROUTINE qqq | `--SCAN t1
# USING COVERING INDEX i2 |--SCAN qqqq `--USE TEMP B-TREE FOR GROUP BY

R-11180-58968-08076-59379-05408-00108-51498-63998 tcl slt th3 src

sqlite> EXPLAIN QUERY PLAN SELECT * FROM (SELECT * FROM t2 WHERE c=1) AS t3, t1;
QUERY PLAN
|--SEARCH t2 USING INDEX i4 (c=?)
`--SCAN t1

/* IMP: R-11180-58968 */

# EVIDENCE-OF: R-11180-58968 sqlite> EXPLAIN QUERY PLAN SELECT * FROM
# (SELECT * FROM t2 WHERE c=1) AS t3, t1; QUERY PLAN |--SEARCH t2 USING
# INDEX i4 (c=?) `--SCAN t1

R-24480-07076-00742-03250-14200-63505-37261-50908 tcl slt th3 src

sqlite> SELECT * FROM
      >   (SELECT * FROM t1 WHERE a=1 ORDER BY b LIMIT 2) AS x,
      >   (SELECT * FROM t2 WHERE c=1 ORDER BY d LIMIT 2) AS y;
QUERY PLAN
|--MATERIALIZE x
|  `--SEARCH t1 USING COVERING INDEX i2 (a=?)
|--MATERIALIZE y
|  |--SEARCH t2 USING INDEX i4 (c=?)
|  `--USE TEMP B-TREE FOR ORDER BY
|--SCAN x
`--SCAN y

/* IMP: R-24480-07076 */

# EVIDENCE-OF: R-24480-07076 sqlite> SELECT * FROM > (SELECT *
# FROM t1 WHERE a=1 ORDER BY b LIMIT 2) AS x, > (SELECT * FROM t2
# WHERE c=1 ORDER BY d LIMIT 2) AS y; QUERY PLAN |--MATERIALIZE x |
# `--SEARCH t1 USING COVERING INDEX i2 (a=?) |--MATERIALIZE y |
# |--SEARCH t2 USING INDEX i4 (c=?) | `--USE TEMP B-TREE FOR ORDER BY
# |--SCAN x `--SCAN y

R-01209-64669-42495-37683-53345-29915-56024-34849 tcl slt th3 src

Each component query of a compound query (UNION, UNION ALL, EXCEPT or INTERSECT) is assigned computed separately and is given its own line in the EXPLAIN QUERY PLAN output.

/* IMP: R-01209-64669 */

# EVIDENCE-OF: R-01209-64669 Each component query of a compound query
# (UNION, UNION ALL, EXCEPT or INTERSECT) is assigned computed
# separately and is given its own line in the EXPLAIN QUERY PLAN output.

R-46219-16052-13457-14850-07693-45237-02962-07960 tcl slt th3 src

sqlite> EXPLAIN QUERY PLAN SELECT a FROM t1 UNION SELECT c FROM t2;
QUERY PLAN
`--COMPOUND QUERY
   |--LEFT-MOST SUBQUERY
   |  `--SCAN t1 USING COVERING INDEX i1
   `--UNION USING TEMP B-TREE
      `--SCAN t2 USING COVERING INDEX i4

/* IMP: R-46219-16052 */

# EVIDENCE-OF: R-46219-16052 sqlite> EXPLAIN QUERY PLAN SELECT a FROM
# t1 UNION SELECT c FROM t2; QUERY PLAN `--COMPOUND QUERY |--LEFT-MOST
# SUBQUERY | `--SCAN t1 USING COVERING INDEX i1 `--UNION USING TEMP
# B-TREE `--SCAN t2 USING COVERING INDEX i4

R-59034-53199-42361-61766-63157-50392-49995-33404 tcl slt th3 src

sqlite> EXPLAIN QUERY PLAN SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1;
QUERY PLAN
`--MERGE (EXCEPT)
   |--LEFT
   |  `--SCAN t1 USING COVERING INDEX i1
   `--RIGHT
      |--SCAN t2
      `--USE TEMP B-TREE FOR ORDER BY

/* IMP: R-59034-53199 */

# EVIDENCE-OF: R-59034-53199 sqlite> EXPLAIN QUERY PLAN SELECT a FROM
# t1 EXCEPT SELECT d FROM t2 ORDER BY 1; QUERY PLAN `--MERGE (EXCEPT)
# |--LEFT | `--SCAN t1 USING COVERING INDEX i1 `--RIGHT |--SCAN t2
# `--USE TEMP B-TREE FOR ORDER BY

SQLite Requirement Matrix Details eqp.html

Index Summary Markup Original

SQLite Requirement Matrix Details
eqp.html