StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I know of no pretty graphical tools, but all of the information you seek is available from the <code>EXPLAIN</code> keyword.</p> <p>Consider this database:</p> <pre> sqlite> create table users (name, email); sqlite> create index user_names on users (name); </pre> <p>A query predicated on <code>email</code> will not use an index:</p> <pre> sqlite> explain select * from users where email='foo'; 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| </pre> <p>Whereas a query predicated on name will use the <code>user_names</code> index:</p> <pre> sqlite> explain select * from users where name='foo'; 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| </pre> <p>Using <code>EXPLAIN</code> does require coming to grips with SQLite's virtual machine, VDBE:</p> <p><a href="http://www.sqlite.org/vdbe.html" rel="nofollow noreferrer">http://www.sqlite.org/vdbe.html</a></p> <p>But this is not as hard as it looks, and gives you the complete story about your query. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload