Small. Fast. Reliable.
Choose any three.

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-12693-31159-47760-14933-14712-45041-12329-60921 tcl slt th3 src

Each SCAN or SEARCH record includes the following information:

/* 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-32349-39610-03152-29035-33006-19037-05076-08294 tcl slt th3 src

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

/* IMP: R-32349-39610 */
# EVIDENCE-OF: R-32349-39610 sqlite> EXPLAIN QUERY PLAN SELECT a, b
# FROM t1 WHERE a=1; QUERY PLAN `--SCAN TABLE t1

R-58477-34656-38121-25387-55134-41708-03146-13097 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 TABLE t1 USING INDEX i1 (a=?)

/* IMP: R-58477-34656 */
# EVIDENCE-OF: R-58477-34656 sqlite> CREATE INDEX i1 ON t1(a);
# sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; QUERY
# PLAN `--SEARCH TABLE t1 USING INDEX i1 (a=?)

R-46193-28368-58972-00027-31729-34447-02569-47363 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 TABLE t1 USING COVERING INDEX i2 (a=?)

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

R-62976-44398-18342-24138-35824-24327-06786-34486 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 TABLE t1 USING INDEX i2 (a=? AND b>?)
`--SCAN TABLE t2

/* IMP: R-62976-44398 */
# EVIDENCE-OF: R-62976-44398 sqlite> EXPLAIN QUERY PLAN SELECT t1.*,
# t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2; QUERY PLAN |--SEARCH
# TABLE t1 USING INDEX i2 (a=? AND b>?) `--SCAN TABLE 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-09866-00585-18081-22279-50210-42819-02133-03966 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 TABLE t1 USING INDEX i2 (a=? AND b>?)
`--SCAN TABLE t2

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

R-60722-16865-19617-65256-11278-03266-08396-03886 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 TABLE t1 USING COVERING INDEX i2 (a=?)
   `--SEARCH TABLE t1 USING INDEX i3 (b=?)

/* IMP: R-60722-16865 */
# EVIDENCE-OF: R-60722-16865 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 TABLE t1 USING COVERING INDEX i2
# (a=?) `--SEARCH TABLE t1 USING INDEX i3 (b=?)

R-02509-11239-04635-35453-11761-20226-62835-31660 tcl slt th3 src

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

/* IMP: R-02509-11239 */
# EVIDENCE-OF: R-02509-11239 sqlite> EXPLAIN QUERY PLAN SELECT c, d
# FROM t2 ORDER BY c; QUERY PLAN |--SCAN TABLE t2 `--USE TEMP B-TREE FOR
# ORDER BY

R-03149-51030-29374-35692-47686-21345-30102-08346 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 TABLE t2 USING INDEX i4

/* IMP: R-03149-51030 */
# EVIDENCE-OF: R-03149-51030 sqlite> CREATE INDEX i4 ON t2(c);
# sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c; QUERY
# PLAN `--SCAN TABLE t2 USING INDEX i4

R-03265-02788-40308-03087-09490-44543-57218-37150 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 TABLE t1 USING COVERING INDEX i2 (a=?)
`--CORRELATED SCALAR SUBQUERY
   `--SEARCH TABLE t1 USING INDEX i3 (b=?)

/* IMP: R-03265-02788 */
# EVIDENCE-OF: R-03265-02788 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 TABLE t1 USING COVERING INDEX i2 (a=?) `--CORRELATED SCALAR
# SUBQUERY `--SEARCH TABLE t1 USING INDEX i3 (b=?)

R-35187-29937-54093-33401-20341-46072-51751-40097 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) GROUP BY x;
QUERY PLAN
|--CO-ROUTINE 0x20FC3E0
|  `--SCAN TABLE t1 USING COVERING INDEX i2
|--SCAN SUBQUERY 0x20FC3E0
`--USE TEMP B-TREE FOR GROUP BY

/* IMP: R-35187-29937 */
# EVIDENCE-OF: R-35187-29937 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)
# GROUP BY x; QUERY PLAN |--CO-ROUTINE 0x20FC3E0 | `--SCAN TABLE t1
# USING COVERING INDEX i2 |--SCAN SUBQUERY 0x20FC3E0 `--USE TEMP B-TREE
# FOR GROUP BY

R-17287-50357-43174-57829-08409-00389-08180-30252 tcl slt th3 src

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

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

R-64351-21396-30235-21975-23610-30773-00421-25207 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 0x18F06F0
|  `--SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)
|--MATERIALIZE 0x18F80D0
|  |--SEARCH TABLE t2 USING INDEX i4 (c=?)
|  `--USE TEMP B-TREE FOR ORDER BY
|--SCAN SUBQUERY 0x18F06F0 AS x
`--SCAN SUBQUERY 0x18F80D0 AS y

/* IMP: R-64351-21396 */
# EVIDENCE-OF: R-64351-21396 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
# 0x18F06F0 | `--SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)
# |--MATERIALIZE 0x18F80D0 | |--SEARCH TABLE t2 USING INDEX i4 (c=?) |
# `--USE TEMP B-TREE FOR ORDER BY |--SCAN SUBQUERY 0x18F06F0 AS x
# `--SCAN SUBQUERY 0x18F80D0 AS 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-48014-15694-55612-58237-43071-46388-26537-49429 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 TABLE t1 USING COVERING INDEX i1
   `--UNION USING TEMP B-TREE
      `--SCAN TABLE t2 USING COVERING INDEX i4

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

R-53201-15548-30250-52937-65049-09366-55722-10298 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 TABLE t1 USING COVERING INDEX i1
   `--RIGHT
      |--SCAN TABLE t2
      `--USE TEMP B-TREE FOR ORDER BY

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