On Mon, Apr 6, 2009 at 3:11 PM, Scott Baker <[email protected]> wrote:
> Eric Minbiole wrote:
>>> If I have a query:
>>>
>>> SELECT foo FROM bar WHERE id = 14;
>>>
>>> How can I see if that query is optimized to use an index, and which index
>>> it's using. I thought if you did an EXPLAIN it would show that, but I'm not
>>> seeing it? Maybe it's not really using an index?
>>
>> Use the command "EXPLAIN QUERY PLAN {your sql command}". This will give
>> you a high level overview of all the tables being accessed, and which
>> indices (if any) will be used for each.
>>
>> For a simple query like your example, SQLite will almost certainly use
>> an index on "id" if one is available. You can use above to verify this.
>
> That did exactly what I needed... and it pointed out that I *wasn't* using
> an index. I didn't realize "INTEGER PRIMARY KEY" was case sensitive. Thanks
> for the help.
>
sql is CaSe-iNsenSitive. See below...
[03:32 PM] ~$sqlite3
SQLite version 3.6.11
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE bar (id InTeGeR pRiMaRy KeY, foo text);
sqlite> .s
CREATE TABLE bar (id InTeGeR pRiMaRy KeY, foo text);
sqlite> INSERT INTO bar (foo) VALUES ('ya');
sqlite> INSERT INTO bar (foo) VALUES ('ba');
sqlite> INSERT INTO bar (id, foo) VALUES (14, 'ca');
sqlite> SELECT * FROM bar;
id foo
---------- ----------
1 ya
2 ba
14 ca
sqlite> SELECT foo FROM bar WHERE id = 14;
foo
----------
ca
sqlite> EXPLAIN QUERY PLAN SELECT foo FROM bar WHERE id = 14;
order from detail
---------- ---------- ---------------------------
0 0 TABLE bar USING PRIMARY KEY
sqlite>
--
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Carbon Model http://carbonmodel.org/
Open Source Geospatial Foundation http://www.osgeo.org/
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
