SQLite - EXPLAIN

The SQLite statement can be preceded by the keyword EXPLAIN or by the phrase EXPLAIN QUERY PLAN used for describing the details of a table.

Either modification causes the SQLite statement to behave as a query and to return information about how the SQLite statement would have operated if the EXPLAIN or EXPLAIN QUERY PLAN keyword had been omitted.

The output from EXPLAIN and EXPLAIN QUERY PLAN is intended for interactive analysis and debugging only.
The details of the output format are subject to change from one release of SQLite to the next.
Applications should not use EXPLAIN or EXPLAIN QUERY PLAN since their exact behavior is variable and only partially documented.

Syntax

The syntax for using EXPLAIN statement in SQLite is given below:

EXPLAIN [SQLite Query]

The syntax for EXPLAIN QUERY PLAN is as follows:

EXPLAIN QUERY PLAN [SQLite Query]

Example:

Consider a database table called Employee with the following records:

EmpID	Name	City	Age	Salary
1	John	London	25	3000
2	Marry	New York	24	2750
3	Jo	Paris	27	2800
4	Kim	Amsterdam	30	3100
5	Ramesh	New Delhi	28	3000
6	Huang	Beijing	28	2800

Now, let's check the result of following EXPLAIN with SELECT statement:

sqlite> EXPLAIN SELECT * FROM Employee;

This will produce the result as shown below:

addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     11    0                    0   Start at 11
1     OpenRead       0     2     0     5              0   root=2 iDb=0; Employee
2     Rewind         0     10    0                    0   
3     Column         0     0     1                    0   r[1]=Employee.EmpID
4     Column         0     1     2                    0   r[2]=Employee.Name
5     Column         0     2     3                    0   r[3]=Employee.City
6     Column         0     3     4                    0   r[4]=Employee.Age
7     Column         0     4     5                    0   r[5]=Employee.Salary
8     ResultRow      1     5     0                    0   output=r[1..5]
9     Next           0     3     0                    1   
10    Halt           0     0     0                    0   
11    Transaction    0     0     4     0              1   usesStmtJournal=0
12    Goto           0     1     0                    0

Now, let's check the following EXPLAIN QUERY PLAN with SELECT statement:

sqlite> EXPLAIN QUERY PLAN SELECT * FROM Employee WHERE Salary >= 2800;

This result of the following code will be:

QUERY PLAN
`--SCAN Employee