How can I analyse a Sqlite query execution?
As outis said:
EXPLAIN QUERY PLAN SELECT * FROM FOO
Does the trick with a more readable output, if like me you're simply using it to ensure you're hitting your indexes (indices?)
I know of no pretty graphical tools, but all of the information you seek is available from the EXPLAIN
keyword.
Consider this database:
sqlite> create table users (name, email);sqlite> create index user_names on users (name);
A query predicated on email
will not use an index:
sqlite> explain select * from users where email='foo';
addr | opcode | p1 | p2 | p3 | p4 | p5 | comment |
---|---|---|---|---|---|---|---|
0 | Trace | 0 | 0 | 0 | 00 | ||
1 | String8 | 0 | 1 | 0 | foo | 00 | |
2 | Goto | 0 | 13 | 0 | 00 | ||
3 | OpenRead | 0 | 2 | 0 | 2 | 00 | |
4 | Rewind | 0 | 11 | 0 | 00 | ||
5 | Column | 0 | 1 | 2 | 00 | ||
6 | Ne | 1 | 10 | 2 | collseq(BINARY) | 6a | |
7 | Column | 0 | 0 | 4 | 00 | ||
8 | Column | 0 | 1 | 5 | 00 | ||
9 | ResultRow | 4 | 2 | 0 | 00 | ||
10 | Next | 0 | 5 | 0 | 01 | ||
11 | Close | 0 | 0 | 0 | 00 | ||
12 | Halt | 0 | 0 | 0 | 00 | ||
13 | Transaction | 0 | 0 | 0 | 00 | ||
14 | VerifyCookie | 0 | 5 | 0 | 00 | ||
15 | TableLock | 0 | 2 | 0 | users | 00 | |
16 | Goto | 0 | 3 | 0 | 00 |
Whereas a query predicated on name will use the user_names
index:
sqlite> explain select * from users where name='foo';
addr | opcode | p1 | p2 | p3 | p4 | p5 | comment |
---|---|---|---|---|---|---|---|
0 | Trace | 0 | 0 | 0 | 00 | ||
1 | String8 | 0 | 1 | 0 | foo | 00 | |
2 | Goto | 0 | 18 | 0 | 00 | ||
3 | OpenRead | 0 | 2 | 0 | 2 | 00 | |
4 | OpenRead | 1 | 3 | 0 | keyinfo(1,BINARY) | 00 | |
5 | IsNull | 1 | 15 | 0 | 00 | ||
6 | Affinity | 1 | 1 | 0 | bb | 00 | |
7 | SeekGe | 1 | 15 | 1 | 1 | 00 | |
8 | IdxGE | 1 | 15 | 1 | 1 | 01 | |
9 | IdxRowid | 1 | 2 | 0 | 00 | ||
10 | Seek | 0 | 2 | 0 | 00 | ||
11 | Column | 1 | 0 | 3 | 00 | ||
12 | Column | 0 | 1 | 4 | 00 | ||
13 | ResultRow | 3 | 2 | 0 | 00 | ||
14 | Next | 1 | 8 | 0 | 00 | ||
15 | Close | 0 | 0 | 0 | 00 | ||
16 | Close | 1 | 0 | 0 | 00 | ||
17 | Halt | 0 | 0 | 0 | 00 | ||
18 | Transaction | 0 | 0 | 0 | 00 | ||
19 | VerifyCookie | 0 | 5 | 0 | 00 | ||
20 | TableLock | 0 | 2 | 0 | users | 00 | |
21 | Goto | 0 | 3 | 0 | 00 |
Using EXPLAIN
does require coming to grips with SQLite's virtual machine, VDBE:
http://www.sqlite.org/opcode.html
But this is not as hard as it looks, and gives you the complete story about your query.
There's this nice graphical tool
https://github.com/asutherland/grok-sqlite-explain
Here's example of output:
And associated blogpost:http://www.visophyte.org/blog/2010/04/06/performance-annotated-sqlite-explaination-visualizations-using-systemtap/