SQLite Explain (Explanation)

Before an SQLite statement, the "EXPLAIN" keyword or the "EXPLAIN QUERY PLAN" phrase can be used to describe the details of the table.

If the EXPLAIN keyword or phrase is omitted, any modifications will trigger the query behavior of the SQLite statement and return information about how the SQLite statement operates.

  • The output from EXPLAIN and EXPLAIN QUERY PLAN is only used for interactive analysis and troubleshooting.

  • The details of the output format may change with different versions of SQLite.

  • Applications should not use EXPLAIN or EXPLAIN QUERY PLAN, because their exact behavior is variable and only partially documented.

Syntax

EXPLAINThe syntax is as follows:

EXPLAIN [SQLite Query]

EXPLAIN QUERY PLANThe syntax is as follows:

EXPLAIN  QUERY PLAN [SQLite Query]

Example

Assume the COMPANY table has the following records:

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Now, let's check the SELECT statement'sExplainusage:

sqlite> EXPLAIN SELECT *  FROM COMPANY  WHERE Salary >= 20000;

This will produce the following results:

addr        opcode      p1          p2          p3
----------  ----------  ----------  ----------  ----------
0           Goto        0           19
1           Integer     0           0
2           OpenRead    0           8
3           SetNumColu  0           5
4           Rewind      0           17
5           Column      0           4
6           RealAffini  0           0
7           Integer     20000       0
8           Lt          357         16          collseq(BI
9           Rowid       0           0
10          Column      0           1
11          Column      0           2
12          Column      0           3
13          Column      0           4
14          RealAffini  0           0
15          Callback    5           0
16          Next        0           5
17          Close       0           0
18          Halt        0           0
19          Transactio  0           0
20          VerifyCook  0           38
21          Goto        0           1
22          Noop        0           0

Now, let's check the SELECT statement'sExplain Query Planusage:

SQLite> EXPLAIN QUERY PLAN SELECT * FROM COMPANY WHERE Salary &gt= 20000;

order       from        detail
----------  ----------  -------------
0           0           TABLE COMPANY
Other Extensions