On 07/17/2011 09:50 AM, Simon Slavin wrote: > > 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.
Thanks. That makes sense now. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
