WL#906: Warn user for inappropriate key creation
Status: Un-Assigned — Priority: Medium
Currently user can define two indexes with exactly same type and column layout. (That is, each index has the same columns in the same order.) Duplicate index definitions just worsen performance and nothing else. It is a typical beginner error so it is good to have this just blocked: Example: CREATE TABLE `t` ( `i` int(11) NOT NULL default '0', KEY `i` (`i`), KEY `i_2` (`i`), KEY `i_3` (`i`), KEY `i_4` (`i`) ) This shall be disallowed, at least ALTER TABLE ADD KEY(i) shall be. The second typical error is creating keys with the same initial column. For example: (a) (a,b) (a,b,c) In some cases having such keys make sense so it is not good to block it completely, however it would be good to produce a warning when such a key is added. The same is true if one uses prefixes, for example: (a(10)) (a(20)) Perhaps two indexes are different if one is UNIQUE and the other is not [Carsten:] Also, don't forget to take into account that we may introduce other types of indexes in time (hashed indexes, bitmap indexes). As a compatibility note, Oracle does not allow you to create two indexes on the same single column -- even when they are of different types, and (as I understand it) not even one on (a) if (a, b) is already defined. [ Noted added 2010-06-15 ] The following is copied from WL#3997 "Warn if duplicate index", which was cancelled today. " During CREATE INDEX, MySQL will check whether there is already an index on the same table, on the same columns (and if 'prefix indexes' then for the same prefixes), of the same type (btree/hash/rtree), with the same characteristic (unique/fulltext/spatial), regardless of 'key block size' or 'with parser' clauses. If a duplicate is found, MySQL will output the warning: "An index named %s already exists for the same columns". This suggestion arises due to remarks in the original LLD for WL#148 ("foreign keys for all storage engines"). "
Copyright (c) 2000, 2018, Oracle Corporation and/or its affiliates. All rights reserved.