1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
3
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
4
<title>EXPLAIN QUERY PLAN</title>
5
<style type="text/css">
8
font-family: Verdana, sans-serif;
13
a:visited { color: #734559 }
15
.logo { position:absolute; margin:3px; }
31
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
32
.toolbar a:visited { color: white; }
33
.toolbar a:hover { color: #044a64; background: white; }
35
.content { margin: 5%; }
36
.content dt { font-weight:bold; }
37
.content dd { margin-bottom: 25px; margin-left:20%; }
38
.content ul { padding:0px; padding-left: 15px; margin:0px; }
41
.se { background: url(images/se.gif) 100% 100% no-repeat #044a64}
42
.sw { background: url(images/sw.gif) 0% 100% no-repeat }
43
.ne { background: url(images/ne.gif) 100% 0% no-repeat }
44
.nw { background: url(images/nw.gif) 0% 0% no-repeat }
46
/* Things for "fancyformat" documents start here. */
47
.fancy img+p {font-style:italic}
48
.fancy .codeblock i { color: darkblue; }
49
.fancy h1,.fancy h2,.fancy h3,.fancy h4 {font-weight:normal;color:#044a64}
50
.fancy h2 { margin-left: 10px }
51
.fancy h3 { margin-left: 20px }
52
.fancy h4 { margin-left: 30px }
53
.fancy th {white-space:nowrap;text-align:left;border-bottom:solid 1px #444}
54
.fancy th, .fancy td {padding: 0.2em 1ex; vertical-align:top}
55
.fancy #toc a { color: darkblue ; text-decoration: none }
56
.fancy .todo { color: #AA3333 ; font-style : italic }
57
.fancy .todo:before { content: 'TODO:' }
58
.fancy p.todo { border: solid #AA3333 1px; padding: 1ex }
59
.fancy img { display:block; }
60
.fancy :link:hover, .fancy :visited:hover { background: wheat }
61
.fancy p,.fancy ul,.fancy ol { margin: 1em 5ex }
62
.fancy li p { margin: 1em 0 }
63
/* End of "fancyformat" specific rules. */
69
<div><!-- container div to satisfy validator -->
72
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite Logo"
74
<div><!-- IE hack to prevent disappearing logo--></div>
75
<div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div>
77
<table width=100% style="clear:both"><tr><td>
78
<div class="se"><div class="sw"><div class="ne"><div class="nw">
79
<table width=100% style="padding:0;margin:0;cell-spacing:0"><tr>
82
<a href="about.html">About</a>
83
<a href="sitemap.html">Sitemap</a>
84
<a href="docs.html">Documentation</a>
85
<a href="download.html">Download</a>
86
<a href="copyright.html">License</a>
87
<a href="news.html">News</a>
88
<a href="support.html">Support</a>
91
gMsg = "Search SQLite Docs..."
92
function entersearch() {
93
var q = document.getElementById("q");
94
if( q.value == gMsg ) { q.value = "" }
95
q.style.color = "black"
96
q.style.fontStyle = "normal"
98
function leavesearch() {
99
var q = document.getElementById("q");
100
if( q.value == "" ) {
102
q.style.color = "#044a64"
103
q.style.fontStyle = "italic"
108
<div style="padding:0 1em 0px 0;white-space:nowrap">
109
<form name=f method="GET" action="http://www.sqlite.org/search">
110
<input id=q name=q type=text
111
onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">
112
<input type=submit value="Go" style="border:solid white 1px;background-color:#044a64;color:white;font-size:0.9em;padding:0 1ex">
116
</div></div></div></div>
118
<div class=startsearch></div>
126
<div style="font-size:2em;text-align:center;color:#044a64">
129
<div style="font-size:1.5em;margin:1em;color:#044a64">
130
Table Of Contents</div>
131
<div id=toc> <div style="margin-left:6ex"><a href="#section_1">1. The EXPLAIN QUERY PLAN Command</a></div><div style="margin-left:12ex"><a href="#section_1_1">1.1. Table and Index Scans</a></div><div style="margin-left:12ex"><a href="#section_1_2">1.2. Temporary Sorting B-Trees</a></div><div style="margin-left:12ex"><a href="#section_1_3">1.3. Subqueries</a></div><div style="margin-left:12ex"><a href="#section_1_4">1.4. Compound Queries</a></div><div style="margin-left:6ex"><a href="#section_2">2. Sample Code</a></div> </div>
132
<div class=startsearch></div>
135
<h1 id="section_1">1. The EXPLAIN QUERY PLAN Command</h1>
137
<p style="margin-left:10ex;margin-right:10ex">
138
<b>Warning: The data returned by the EXPLAIN QUERY PLAN command is
139
intended for interactive debugging only. It may change dramatically between
140
SQLite releases. Applications should not depend on the results of an EXPLAIN
141
QUERY PLAN command.</b>
143
<p>The <a href="lang_explain.html">EXPLAIN QUERY PLAN</a> SQL command is used to obtain a high-level
144
description of the strategy or plan that SQLite uses to implement a specific
145
SQL query. Most significantly, it reports on the way in which the query uses
146
database indices. In interpreting and using this information to optimize
147
database schemas and queries, users might find the documents describing how
148
SQLite <a href="queryplanner.html">plans</a> and <a href="optoverview.html">optimizes</a> queries useful.
150
<p>An EXPLAIN QUERY PLAN command returns zero or more rows of four columns
151
each. The column names are "selectid", "order", "from", "detail". The
152
first three columns contain an integer value. The final
153
column, "detail", contains a text value which carries most of
154
the useful information.
156
<p>EXPLAIN QUERY PLAN is most useful on a SELECT statement,
157
but may also be appear with other statements that read data from database
158
tables (e.g. UPDATE, DELETE, INSERT INTO ... SELECT).
160
<h2 id="section_1_1">1.1. Table and Index Scans</h2>
163
When processing a SELECT (or other) statement, SQLite may retrieve data from
164
database tables in a variety of ways. It may scan through all the records in
165
a table (a full-table scan), scan a contiguous subset of the records in a
166
table based on the rowid index, scan a contiguous subset of the entries in a
167
database <a href="lang_createtable.html">index</a>, or use a combination of the above strategies
168
in a single scan. The various ways in which SQLite may retrieve data from a
169
table or index are described in detail <a href="queryplanner.html#searching">here</a>.
172
For each table read by the query, the output of EXPLAIN QUERY
173
PLAN includes a record for which the value in the "detail" column begins
174
with either "SCAN" or "SEARCH". "SCAN" is used for a full-table scan,
175
including cases where SQLite iterates through all records in a table
176
in an order defined by an index. "SEARCH" indicates that only a subset of
177
the table rows are visited. Each SCAN or SEARCH record includes the
178
following information:
181
<li> The name of the table data is read from.
182
<li> Whether or not an index or <a href="optoverview.html#autoindex">automatic index</a> is used.
183
<li> Whether or not the <a href="queryplanner.html#covidx">covering index</a> optimization applies.
184
<li> Which terms of the WHERE clause are used for indexing.
185
<li> The estimated number of rows that SQLite will visit.
189
For example, the following EXPLAIN QUERY PLAN command operates on a SELECT
190
statement that is implemented by performing a full-table scan on table t1:
191
<div class=codeblock style="margin:0 15ex"><table width=100% style="border:1px solid #80a796;padding:0 1ex;background-color:#EEEEEE"><tr><td><pre style="font-size:1.1em">sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
192
0|0|0|SCAN TABLE t1 (~100000 rows)
196
The example above shows
197
SQLite estimating that the full-table scan will visit approximately
198
100,000 records. If the query were able to use an index, then the
199
SCAN/SEARCH record would include the name of the index and, for a
200
SEARCH record, an indication of how the subset of rows visited is
201
identified. For example:
202
<div class=codeblock style="margin:0 15ex"><table width=100% style="border:1px solid #80a796;padding:0 1ex;background-color:#EEEEEE"><tr><td><pre style="font-size:1.1em">sqlite> CREATE INDEX i1 ON t1(a);
203
sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
204
0|0|0|SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)
208
The previous example, SQLite uses index "i1" to optimize
209
a WHERE clause term of the form (a=?) - in this case "a=1". SQLite
210
estimates that about 10 records will match the "a=1" term.
211
The previous example could not use a <a href="queryplanner.html#covidx">covering index</a>, but the following
212
example can, and that fact is reflected in the output:
213
<div class=codeblock style="margin:0 15ex"><table width=100% style="border:1px solid #80a796;padding:0 1ex;background-color:#EEEEEE"><tr><td><pre style="font-size:1.1em">sqlite> CREATE INDEX i2 ON t1(a, b);
214
sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
215
0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)
219
All joins in SQLite are <a href="optoverview.html#table_order">implemented using nested scans</a>. When a
220
SELECT query that features a join is analyzed using EXPLAIN QUERY PLAN, one
221
SCAN or SEARCH record is output for each nested loop. For example:
222
<div class=codeblock style="margin:0 15ex"><table width=100% style="border:1px solid #80a796;padding:0 1ex;background-color:#EEEEEE"><tr><td><pre style="font-size:1.1em">sqlite> EXPLAIN QUERY PLAN SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2;
223
0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~3 rows)
224
0|1|1|SCAN TABLE t2 (~1000000 rows)
228
The second column of output (column "order") indicates the nesting order. In
229
this case, the scan of table t1 using index i2 is the outer loop (order=0)
230
and the full-table scan of table t2 (order=1) is the inner loop. The third
231
column (column "from"), indicates the position in the FROM clause of the
232
SELECT statement that the table associated with each scan occurs in. In the
233
case above, table t1 occupies the first position in the FROM clause, so the
234
value of column "from" is 0 in the first record. Table t2 is in the
235
second position, so the "from" column for the corresponding SCAN record is
236
set to 1. In the following example, the positions of t1 and t2 in the FROM
237
clause of the SELECT are reversed. The query strategy remains the same, but
238
the values in the "from" column of the output are adjusted accordingly.
239
<div class=codeblock style="margin:0 15ex"><table width=100% style="border:1px solid #80a796;padding:0 1ex;background-color:#EEEEEE"><tr><td><pre style="font-size:1.1em">sqlite> EXPLAIN QUERY PLAN SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2;
240
0|0|1|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~3 rows)
241
0|1|0|SCAN TABLE t2 (~1000000 rows)
245
In the example above, SQLite estimates that the outer loop scan will visit
246
approximately 3 rows, and that the inner loop will visit
247
approximately 1,000,000. If
248
you observe that SQLite's estimates are wildly inaccurate (and appear to be
249
causing it to generate sub-optimal query plans), your queries may benefit
250
from running the <a href="lang_analyze.html">ANALYZE</a> command on the database.
253
If the WHERE clause of a query contains an OR expression, then SQLite might
254
use the <a href="queryplanner.html#or_in_where">"OR by union"</a> strategy (also described
255
<a href="optoverview.html#or_opt">here</a>). In this case there will be two SEARCH records, one
256
for each index, with the same values in both the "order" and "from" columns.
258
<div class=codeblock style="margin:0 15ex"><table width=100% style="border:1px solid #80a796;padding:0 1ex;background-color:#EEEEEE"><tr><td><pre style="font-size:1.1em">sqlite> CREATE INDEX i3 ON t1(b);
259
sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2;
260
0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)
261
0|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)
264
<h2 id="section_1_2">1.2. Temporary Sorting B-Trees</h2>
267
If a SELECT query contains an ORDER BY, GROUP BY or DISTINCT clause,
268
SQLite may need to use a temporary b-tree structure to sort the output
269
rows. Or, it might <a href="queryplanner.html#sorting">use an index</a>. Using an index is
270
almost always much more efficient than performing a sort.
271
If a temporary b-tree is required, a record is added to the EXPLAIN
272
QUERY PLAN output with the "detail" field set to a string value of
273
the form "USE TEMP B-TREE FOR xxx", where xxx is one of "ORDER BY",
274
"GROUP BY" or "DISTINCT". For example:
276
<div class=codeblock style="margin:0 15ex"><table width=100% style="border:1px solid #80a796;padding:0 1ex;background-color:#EEEEEE"><tr><td><pre style="font-size:1.1em">sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c;
277
0|0|0|SCAN TABLE t2 (~1000000 rows)
278
0|0|0|USE TEMP B-TREE FOR ORDER BY
282
In this case using the temporary b-tree can be avoided by creating an index
283
on t2(c), as follows:
285
<div class=codeblock style="margin:0 15ex"><table width=100% style="border:1px solid #80a796;padding:0 1ex;background-color:#EEEEEE"><tr><td><pre style="font-size:1.1em">sqlite> CREATE INDEX i4 ON t2(c);
286
sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c;
287
0|0|0|SCAN TABLE t2 USING INDEX i4 (~1000000 rows)
290
<h2 id="section_1_3">1.3. Subqueries</h2>
293
In all the examples above, the first column (column "selectid") is always
294
set to 0. If a query contains sub-selects, either as part of the FROM
295
clause or as part of SQL expressions, then the output of EXPLAIN QUERY
296
PLAN also includes a report for each sub-select. Each sub-select is assigned
297
a distinct, non-zero "selectid" value. The top-level SELECT statement is
298
always assigned the selectid value 0. For example:
300
<div class=codeblock style="margin:0 15ex"><table width=100% style="border:1px solid #80a796;padding:0 1ex;background-color:#EEEEEE"><tr><td><pre style="font-size:1.1em">sqlite> EXPLAIN QUERY PLAN SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2;
301
0|0|0|SCAN TABLE t2 (~1000000 rows)
302
0|0|0|EXECUTE SCALAR SUBQUERY 1
303
1|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)
304
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
305
2|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)
309
The example above contains a pair of scalar subqueries assigned selectid
310
values 1 and 2. As well as a SCAN record, there are also 2 "EXECUTE"
311
records associated with the top level subquery (selectid 0), indicating
312
that subqueries 1 and 2 are executed by the top level query in a scalar
313
context. The CORRELATED qualifier present in the EXECUTE record associated
314
with scalar subquery 2 indicates that the query must be run separately
315
for each row visited by the top level query. Its absence in the record
316
associated with subquery 1 means that the subquery is only run once and
317
the result cached. In other words, subquery 2 may be more performance
318
critical, as it may be run many times whereas subquery 1 is only ever run
322
Unless the <a href="optoverview.html#flattening">flattening optimization</a> is applied, if a subquery appears in
323
the FROM clause of a SELECT statement, SQLite executes the subquery and
324
stores the results in a temporary table. It then uses the contents of the
325
temporary table in place of the subquery to execute the parent query. This
326
is shown in the output of EXPLAIN QUERY PLAN by substituting a
327
"SCAN SUBQUERY" record for the "SCAN TABLE" record that normally appears
328
for each element in the FROM clause. For example:
330
<div class=codeblock style="margin:0 15ex"><table width=100% style="border:1px solid #80a796;padding:0 1ex;background-color:#EEEEEE"><tr><td><pre style="font-size:1.1em">sqlite> EXPLAIN QUERY PLAN SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x;
331
1|0|0|SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)
332
0|0|0|SCAN SUBQUERY 1 (~1000000 rows)
333
0|0|0|USE TEMP B-TREE FOR GROUP BY
337
If the <a href="optoverview.html#flattening">flattening optimization</a> is used on a subquery in the FROM clause
338
of a SELECT statement, then the output of EXPLAIN QUERY PLAN reflects this.
339
For example, in the following there is no "SCAN SUBQUERY" record even though
340
there is a subquery in the FROM clause of the top level SELECT. Instead, since
341
the flattening optimization does apply in this case, the EXPLAIN QUERY PLAN
342
report shows that the top level query is implemented using a nested loop join
345
<div class=codeblock style="margin:0 15ex"><table width=100% style="border:1px solid #80a796;padding:0 1ex;background-color:#EEEEEE"><tr><td><pre style="font-size:1.1em">sqlite> EXPLAIN QUERY PLAN SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1;
346
0|0|0|SEARCH TABLE t2 USING INDEX i4 (c=?) (~10 rows)
347
0|1|1|SCAN TABLE t1 (~1000000 rows)
350
<h2 id="section_1_4">1.4. Compound Queries</h2>
353
Each component query of a <a href="lang_select.html#compound">compound query</a> (UNION, UNION ALL, EXCEPT or
354
INTERSECT) is assigned its own selectid and reported on separately. A
355
single record is output for the parent (compound query) identifying the
356
operation, and whether or not a temporary b-tree is used to implement
359
<div class=codeblock style="margin:0 15ex"><table width=100% style="border:1px solid #80a796;padding:0 1ex;background-color:#EEEEEE"><tr><td><pre style="font-size:1.1em">sqlite> EXPLAIN QUERY PLAN SELECT a FROM t1 UNION SELECT c FROM t2;
360
1|0|0|SCAN TABLE t1 (~1000000 rows)
361
2|0|0|SCAN TABLE t2 (~1000000 rows)
362
0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)
366
The "USING TEMP B-TREE" clause in the above output indicates that a
367
temporary b-tree structure is used to implement the UNION of the results
368
of the two sub-selects. If the temporary b-tree were not required, as
369
in the following example, the clause is not present.
371
<div class=codeblock style="margin:0 15ex"><table width=100% style="border:1px solid #80a796;padding:0 1ex;background-color:#EEEEEE"><tr><td><pre style="font-size:1.1em">sqlite> EXPLAIN QUERY PLAN SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1;
372
1|0|0|SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)
373
2|0|0|SCAN TABLE t2 (~1000000 rows)
374
2|0|0|USE TEMP B-TREE FOR ORDER BY
375
0|0|0|COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)
378
<h1 id="section_2">2. Sample Code</h1>
380
<p>Sometimes, within a large application, it may be inconvenient to modify
381
code to generate EXPLAIN QUERY PLAN commands for the SELECT queries being
382
investigated. From within an interactive debugging session, it may be almost
383
impossible. In these situations, a function similar to the following may
384
be useful. This particular function is passed an SQLite statement handle as an
385
argument and outputs the corresponding EXPLAIN QUERY PLAN report to standard
386
output. Application specific versions may output the report to an application
390
<div class=codeblock style="margin:0 15ex"><table width=100% style="border:1px solid #80a796;padding:0 1ex;background-color:#EEEEEE"><tr><td><pre style="font-size:1.1em">/*
391
** Argument pStmt is a prepared SQL statement. This function compiles
392
** an EXPLAIN QUERY PLAN command to report on the prepared statement,
393
** and prints the report to stdout using printf().
395
int printExplainQueryPlan(sqlite3_stmt *pStmt){
396
const char *zSql; /* Input SQL */
397
char *zExplain; /* SQL with EXPLAIN QUERY PLAN prepended */
398
sqlite3_stmt *pExplain; /* Compiled EXPLAIN QUERY PLAN command */
399
int rc; /* Return code from sqlite3_prepare_v2() */
401
zSql = sqlite3_sql(pStmt);
402
if( zSql==0 ) return SQLITE_ERROR;
404
zExplain = sqlite3_mprintf("EXPLAIN QUERY PLAN %s", zSql);
405
if( zExplain==0 ) return SQLITE_NOMEM;
407
rc = sqlite3_prepare_v2(sqlite3_db_handle(pStmt), zExplain, -1, &pExplain, 0);
408
sqlite3_free(zExplain);
409
if( rc!=SQLITE_OK ) return rc;
411
while( SQLITE_ROW==sqlite3_step(pExplain) ){
412
int iSelectid = sqlite3_column_int(pExplain, 0);
413
int iOrder = sqlite3_column_int(pExplain, 1);
414
int iFrom = sqlite3_column_int(pExplain, 2);
415
const char *zDetail = (const char *)sqlite3_column_text(pExplain, 3);
417
printf("%d %d %d %s\n", iSelectid, iOrder, iFrom, zDetail);
420
return sqlite3_finalize(pExplain);