On 17 Jul 2011, at 2:40pm, dcharno wrote: > I have a table where columns a and b form a unique key for column c. In > an attempt to speed up queries I added an index on a and b. > > CREATE TABLE t(a TEXT, b TEXT, c TEXT, CONSTRAINT u UNIQUE(a,b)); > CREATE INDEX iab ON t(a, b); > > But, an automatic index is being used even though it seems like the > index terms should be usable according to the optimizer overview: > > sqlite> EXPLAIN QUERY PLAN SELECT * FROM t WHERE a="foo" AND b="bar"; > 0|0|0|SEARCH TABLE t USING INDEX sqlite_autoindex_t_1 (a=? AND b=?) > (~1 rows)
The CONSTRAINT you defined on the TABLE requires SQLite to make up its own index. Because without that index it would have to scan every row of the table whenever you INSERTed a new row to see if there was a clash. Since SQLite already has an idea index for your query it never get as far as noticing that you made another one yourself. Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
