Only when you query X though....querying Y by itself gives a table scan.
SQLite version 3.7.11 2012-03-20 11:35:50 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table tmp(x,y); sqlite> create unique index idx on tmp(x,y); sqlite> explain query plan select * from tmp where x=9; 0|0|0|SEARCH TABLE tmp USING COVERING INDEX idx (x=?) (~10 rows) sqlite> explain query plan select * from tmp where x=9 and y=9; 0|0|0|SEARCH TABLE tmp USING COVERING INDEX idx (x=? AND y=?) (~1 rows) sqlite> explain query plan select * from tmp where y=9; 0|0|0|SCAN TABLE tmp (~100000 rows) Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems ________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Richard Hipp [d...@sqlite.org] Sent: Thursday, June 21, 2012 7:30 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] sqlite compound keys On Thu, Jun 21, 2012 at 1:47 AM, nn6eumtr <nn6eu...@gmail.com> wrote: > > If I have a table: > > create table tmp ( > x integer, > y integer > ) > > And a an index with compound key: > > create unique index idx on tmp (x, y); > > And I did a query: > > select * from tmp where x = 9 > > Would sqlite know how to use the index to find records where x = 9? Or > would it opt for a scan instead? > It will use the index. > > > ______________________________**_________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users<http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users%3Chttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>> > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
