Small. Fast. Reliable.
Choose any three.

SQLite Requirement Matrix Details
eqp.html

Index Summary Markup Original

R-46794-19848-23128-00719-30471-17820-52179-33084 tcl slt th3 src

An EXPLAIN QUERY PLAN command returns zero or more rows of four columns each.

th3/req1/eqp01.test:10

/* IMP: R-46794-19848 */

# EVIDENCE-OF: R-46794-19848 An EXPLAIN QUERY PLAN command returns zero
# or more rows of four columns each.

R-50054-41546-44227-45565-32365-53294-59640-58817 tcl slt th3 src

The column names are "selectid", "order", "from", "detail".

th3/req1/eqp01.test:13

/* IMP: R-50054-41546 */

# EVIDENCE-OF: R-50054-41546 The column names are "selectid", "order",
# "from", "detail".

R-30248-02140-43435-09276-30517-50982-49513-41085 tcl slt th3 src

The first three columns contain an integer value.

th3/req1/eqp01.test:16

/* IMP: R-30248-02140 */

# EVIDENCE-OF: R-30248-02140 The first three columns contain an integer
# value.

R-31327-09584-58286-37804-26894-23464-47071-52660 tcl slt th3 src

The final column, "detail", contains a text value which carries most of the useful information.

th3/req1/eqp01.test:19

/* IMP: R-31327-09584 */

# EVIDENCE-OF: R-31327-09584 The final column, "detail", contains a text
# value which carries most of the useful information.

R-48001-03885-46533-22918-25450-29209-50529-34547 tcl slt th3 src

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

th3/req1/eqp01.test:53

/* IMP: R-48001-03885 */

# EVIDENCE-OF: R-48001-03885 EXPLAIN QUERY PLAN is most useful on a
# SELECT statement, but may also be 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".

th3/req1/eqp01.test:76

/* 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.

th3/req1/eqp01.test:90

/* 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.

th3/req1/eqp01.test:104

/* IMP: R-38242-53217 */

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

R-12693-31159-47760-14933-14712-45041-12329-60921 tcl slt th3 src

Each SCAN or SEARCH record includes the following information:

The name of the table 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.

th3/req1/eqp01.test:26

/* IMP: R-12693-31159 */

# EVIDENCE-OF: R-12693-31159 Each SCAN or SEARCH record includes the
# following information: The name of the table 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-47779-47605-17033-59564-54641-41930-11276-19483 tcl slt th3 src

sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; 
0|0|0|SCAN TABLE t1

tcl/eqp.test:373

/* IMP: R-47779-47605 */

# EVIDENCE-OF: R-47779-47605 sqlite> EXPLAIN QUERY PLAN SELECT a, b
# FROM t1 WHERE a=1; 0|0|0|SCAN TABLE t1

R-55852-17599-19078-62551-62802-26618-54100-32972 tcl slt th3 src

sqlite> CREATE INDEX i1 ON t1(a);
sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
0|0|0|SEARCH TABLE t1 USING INDEX i1

tcl/eqp.test:382

/* IMP: R-55852-17599 */

# EVIDENCE-OF: R-55852-17599 sqlite> CREATE INDEX i1 ON t1(a);
# sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
# 0|0|0|SEARCH TABLE t1 USING INDEX i1

R-21179-11011-35531-44364-04825-01555-54528-36481 tcl slt th3 src

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

tcl/eqp.test:391

/* IMP: R-21179-11011 */

# EVIDENCE-OF: R-21179-11011 sqlite> CREATE INDEX i2 ON t1(a, b);
# sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)

R-09991-48941-34645-31832-04466-19849-36549-45775 tcl slt th3 src

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

tcl/eqp.test:400

/* IMP: R-09991-48941 */

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

R-55443-46660-27592-17981-12219-05081-62305-56714 tcl slt th3 src

The second column of output (column "order") indicates the nesting order.

th3/req1/eqp01.test:184

/* IMP: R-55443-46660 */

# EVIDENCE-OF: R-55443-46660 The second column of output (column
# "order") indicates the nesting order.

R-33626-61085-00249-11901-41489-60441-37285-33795 tcl slt th3 src

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

tcl/eqp.test:411

/* IMP: R-33626-61085 */

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

R-04002-25654-12640-07246-34304-59945-33194-22689 tcl slt th3 src

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

tcl/eqp.test:421

/* IMP: R-04002-25654 */

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

R-24577-38891-65340-59973-62459-58446-28963-28131 tcl slt th3 src

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

tcl/eqp.test:432

/* IMP: R-24577-38891 */

# EVIDENCE-OF: R-24577-38891 sqlite> EXPLAIN QUERY PLAN SELECT c, d
# FROM t2 ORDER BY c; 0|0|0|SCAN TABLE t2 0|0|0|USE TEMP B-TREE FOR
# ORDER BY

R-58157-12355-40365-38560-33320-54735-39734-50208 tcl slt th3 src

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

tcl/eqp.test:442

/* IMP: R-58157-12355 */

# EVIDENCE-OF: R-58157-12355 sqlite> CREATE INDEX i4 ON t2(c);
# sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c;
# 0|0|0|SCAN TABLE t2 USING INDEX i4

R-50329-17145-27119-54152-42841-36375-05497-15742 tcl slt th3 src

If a query contains sub-selects, either as part of the FROM clause or as part of SQL expressions, then the output of EXPLAIN QUERY PLAN also includes a report for each sub-select.

th3/req1/eqp01.test:197

/* IMP: R-50329-17145 */

# EVIDENCE-OF: R-50329-17145 If a query contains sub-selects, either as
# part of the FROM clause or as part of SQL expressions, then the output
# of EXPLAIN QUERY PLAN also includes a report for each sub-select.

R-24958-23411-18929-15714-30148-06066-52554-42233 tcl slt th3 src

Each sub-select is assigned a distinct, non-zero "selectid" value.

th3/req1/eqp01.test:201

/* IMP: R-24958-23411 */

# EVIDENCE-OF: R-24958-23411 Each sub-select is assigned a distinct,
# non-zero "selectid" value.

R-11290-60355-43377-26029-27960-65452-54790-61013 tcl slt th3 src

The top-level SELECT statement is always assigned the selectid value 0.

th3/req1/eqp01.test:204

/* IMP: R-11290-60355 */

# EVIDENCE-OF: R-11290-60355 The top-level SELECT statement is always
# assigned the selectid value 0.

R-13931-10421-35283-26355-06468-08264-20065-34151 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;
0|0|0|SCAN TABLE t2
0|0|0|EXECUTE SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
2|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?)

tcl/eqp.test:451

/* IMP: R-13931-10421 */

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

R-00515-56855-31188-51684-43616-15583-54594-04320 tcl slt th3 src

Unless the flattening optimization is applied, if a subquery appears in the FROM clause of a SELECT statement, SQLite executes the subquery and stores the results in a temporary table. It then uses the contents of the temporary table in place of the subquery to execute the parent query.

th3/req1/eqp01.test:223

/* IMP: R-00515-56855 */

# EVIDENCE-OF: R-00515-56855 Unless the flattening optimization is
# applied, if a subquery appears in the FROM clause of a SELECT
# statement, SQLite executes the subquery and stores the results in a
# temporary table. It then uses the contents of the temporary table in
# place of the subquery to execute the parent query.

R-50892-45943-46066-37296-36419-10149-27380-46800 tcl slt th3 src

sqlite> EXPLAIN QUERY PLAN SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x;
1|0|0|SCAN TABLE t1 USING COVERING INDEX i2
0|0|0|SCAN SUBQUERY 1
0|0|0|USE TEMP B-TREE FOR GROUP BY

tcl/eqp.test:469

/* IMP: R-50892-45943 */

# EVIDENCE-OF: R-50892-45943 sqlite> EXPLAIN QUERY PLAN SELECT
# count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x;
# 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2 0|0|0|SCAN SUBQUERY 1
# 0|0|0|USE TEMP B-TREE FOR GROUP BY

R-46219-33846-03341-47811-62259-36002-31480-38609 tcl slt th3 src

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

tcl/eqp.test:483

/* IMP: R-46219-33846 */

# EVIDENCE-OF: R-46219-33846 sqlite> EXPLAIN QUERY PLAN SELECT * FROM
# (SELECT * FROM t2 WHERE c=1), t1; 0|0|0|SEARCH TABLE t2 USING INDEX i4
# (c=?) 0|1|1|SCAN TABLE t1

R-10717-02538-23214-42555-35648-10956-24290-47354 tcl slt th3 src

Each component query of a compound query (UNION, UNION ALL, EXCEPT or INTERSECT) is assigned its own selectid and reported on separately.

th3/req1/eqp01.test:246

/* IMP: R-10717-02538 */

# EVIDENCE-OF: R-10717-02538 Each component query of a compound query
# (UNION, UNION ALL, EXCEPT or INTERSECT) is assigned its own selectid
# and reported on separately.

R-20533-60939-10698-49029-55085-32598-43205-37289 tcl slt th3 src

A single record is output for the parent (compound query) identifying the operation, and whether or not a temporary b-tree is used to implement it.

th3/req1/eqp01.test:250

/* IMP: R-20533-60939 */

# EVIDENCE-OF: R-20533-60939 A single record is output for the parent
# (compound query) identifying the operation, and whether or not a
# temporary b-tree is used to implement it.

R-37879-39987-64431-01486-31700-04419-20015-25987 tcl slt th3 src

sqlite> EXPLAIN QUERY PLAN SELECT a FROM t1 UNION SELECT c FROM t2;
1|0|0|SCAN TABLE t1
2|0|0|SCAN TABLE t2
0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)

tcl/eqp.test:493

/* IMP: R-37879-39987 */

# EVIDENCE-OF: R-37879-39987 sqlite> EXPLAIN QUERY PLAN SELECT a FROM
# t1 UNION SELECT c FROM t2; 1|0|0|SCAN TABLE t1 2|0|0|SCAN TABLE t2
# 0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)

R-44864-63011-00700-13629-09474-43315-20375-05910 tcl slt th3 src

sqlite> EXPLAIN QUERY PLAN SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1;
1|0|0|SCAN TABLE t1 USING COVERING INDEX i2
2|0|0|SCAN TABLE t2
2|0|0|USE TEMP B-TREE FOR ORDER BY
0|0|0|COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)

tcl/eqp.test:505

/* IMP: R-44864-63011 */

# EVIDENCE-OF: R-44864-63011 sqlite> EXPLAIN QUERY PLAN SELECT a FROM
# t1 EXCEPT SELECT d FROM t2 ORDER BY 1; 1|0|0|SCAN TABLE t1 USING
# COVERING INDEX i2 2|0|0|SCAN TABLE t2 2|0|0|USE TEMP B-TREE FOR ORDER
# BY 0|0|0|COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)

SQLite Requirement Matrix Details eqp.html

Index Summary Markup Original

SQLite Requirement Matrix Details
eqp.html