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