dcharno <[email protected]> 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);
Don't create that index. One is already created for you when you declare a UNIQUE constraint. > 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) > > Even if I disable automatic indexing, its still being created: sqlite_autoindex_t_1 is not an automatic index (meaning a temporary index created on the fly for one particular query). It's a regular permanent index, with an automatically generated name. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
