On 09/20/2012 04:51 PM, Scholz Maik (CM-AI/PJ-CF42) wrote:
Hi,
I have a problem with a missing use of an index.
My Example:
sqlite> create table tab1 (o INT, a INT, b INT,UNIQUE(o,b));
sqlite> select * from sqlite_master;
table|tab1|tab1|2|CREATE TABLE tab1 (o INT, a INT, b INT,UNIQUE(o,b))
index|sqlite_autoindex_tab1_1|tab1|3|
=> I expect that the index "sqlite_autoindex_tab1_1" is on column o and b?
sqlite> explain query plan SELECT * from tab1 WHERE o=1;
0|0|0|SEARCH TABLE tab1 USING INDEX sqlite_autoindex_tab1_1 (o=?) (~10 rows)
=> OK: "sqlite_autoindex_tab1_1" is used
sqlite> explain query plan SELECT * from tab1 WHERE a=1;
0|0|0|SCAN TABLE tab1 (~100000 rows)
=> OK: "sqlite_autoindex_tab1_1" is not used because a is not indexed.
sqlite> explain query plan SELECT * from tab1 WHERE b=1;
0|0|0|SCAN TABLE tab1 (~100000 rows)
=> NOK: Why is "sqlite_autoindex_tab1_1" not used?
Creating an index basically creates a sorted list on disk. In your
case, the elements of the list are sorted in order of column "o", with
column "b" used as a tie-breaker. In the same way as the entries in
a phone book are sorted by surname with the first name or initial of
the subscriber used as a tie-breaker.
So with a phone book, you can do these easily enough:
SELECT * FROM subscribers WHERE surname = 'Smith';
SELECT * FROM subscribers WHERE surname = 'Smith' AND fname = 'Joe';
But this is hard:
SELECT * FROM subscribers WHERE fname = 'Joe';
To find all subscribers named 'Joe', you would have to search the whole
phone book from beginning to end. The fact that it's in sorted order
would not help.
Perhaps you need a second index on column "b" only.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
