On Thursday, 2 March, 2017 06:04, Hick Gunter <h...@scigames.at> wrote:
> 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); Whatever is the reason for creating index plain? There is already an index specified by the use of the "unique" keyword in the table definition. Get rid of it. It is just a waste of space and serves no purpose (other than to confuse you). > NB: The field name has a unique constraint Yes -- and to enforce that constraint (or a primary key constraint where the key is NOT a simple integer primary key alias for the RowID) a unique index is automatically created called sqlite_autoindex_<tablename>_<sequence#>. Since this is the first such index needed by the table definition the index would be called sqlite_autoindex_test_1. > 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) It happens to be covering yes, but it is chosen because it contains the two things referenced in the WHERE clauses. > 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) No it does not. This is the index created when the table was defined to enforce your unique constraint. When an index is created temporarily to service a query you will see "USING AUTOMATIC COVERING INDEX" -- the magic word is AUTOMATIC. > >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. No, it is quite common. However, you need to not muddy the waters by creating duplicate indexes (sqlite will not get confused, however you will confuse yourself), and also run ANALYZE to see if that makes a difference. However, there may also be a wee issue (that I noticed elsewhere) that intentional covering indexes don't seem to always be selected properly if they are declared unique (even if they are unique). Try declaring your covering index without the unique constraint and see if that makes a difference. Maybe this is something Richard can address -- whether or not there is a preference in the covering index selection for a non-unique vs unique index. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
