I tried to create a test table and two indices thus: >create temp table test (id integer primary key, name text unique, bs integer, >data text); >create unique index plain on test(name); >create unique index cover on test(name,bs);
NB: The field name has a unique constraint As long as the query mentions the additional field, sqlite chooses the covering index. >explain query plan select name,bs from test where name='test' and bs=1; >sele order from deta >---- ------------- ---- ---- >0 0 0 SEARCH TABLE test USING COVERING INDEX cover >(name=? AND bs=?) (~1 rows) unfortunately if thsi field is dropped from the where clause but remains in the select list, sqlite misses the covering index >explain query plan select name,bs from test where name='test'; >sele order from deta >---- ------------- ---- ---- >0 0 0 SEARCH TABLE test USING INDEX plain (name=?) (~1 >rows) not having an index at alls causes sqlite to create an automatic index >drop index cover; >drop index plain; >explain query plan select name,bs from test where name='test'; >sele order from deta >---- ------------- ---- ---- >0 0 0 SEARCH TABLE test USING INDEX >sqlite_autoindex_test_1 (name=?) (~1 rows) just the same if the index is not unique (even though the first field is) >create index plain on test(name); >create index cover on test(name,bs); >explain query plan select name,bs from test where name='test'; >sele order from deta >---- ------------- ---- ---- >0 0 0 SEARCH TABLE test USING INDEX >sqlite_autoindex_test_1 (name=?) (~1 rows) I guess such a schema would be quite unusual, so this optimization opportunity is missed -----Ursprüngliche Nachricht----- >"Any Index that starts with a Unique column will by definition be Unique." >Sorry, yet, I admit the title of the thread is confusing. What I meant to say >is that it's TOO unique :). Adding the additional columns will allow >duplicates on the columns where duplicates should not be allowed. I don't think so. There can only be one entry for the unique field, and the extra column captures the corresponding value of the one and only matching record. ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: [email protected] This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
