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 
(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:  
  `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:  
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:  
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").