How can I analyse a Sqlite query execution?

Question

I have a Sqlite database which I want to check the indexes are correct. MS SQL Analyser is great at breaking down the query execution and utilised indexes.

Is there a similar tool for Sqlite?

Answer 1

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?)

Answer 2

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.