Latest web development tutorials

SQLite Explain

Before SQLite statement, you can use the "EXPLAIN" keyword or "EXPLAIN QUERY PLAN" phrase, for details description.

If you omit the EXPLAIN keyword or phrase, any changes will cause the query behavior SQLite statement, and returns information about how to do SQLite statement.

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

  • Details of the output format may vary depending on the version of SQLite vary.

  • Applications should not use EXPLAIN or EXPLAIN QUERY PLAN, because its exact behavior is variable and will be only partially recorded.

grammar

EXPLAIN syntax is as follows:

EXPLAIN [SQLite Query]

EXPLAIN QUERY PLAN syntax is as follows:

EXPLAIN QUERY PLAN [SQLite Query]

Examples

Suppose 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 examine the SELECT statementExplain the use of:

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

This produces 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 examine the SELECT statement using theExplain Query Plan:

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

order from detail
---------- ---------- -------------
0 0 TABLE COMPANY