Bug #37520 CREATE TABLE: no warning if requested duplicate index for the same column
Submitted: 19 Jun 2008 11:30 Modified: 30 Jul 2012 17:58
Reporter: Vincent Ricard Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.0, 5.1 OS:Linux
Assigned to:
Tags: INDEX, unique constraint
Triage: Triaged: D3 (Medium)

[19 Jun 2008 11:30] Vincent Ricard
Description:
The following DDL creates the table with 2 indices on the same column:
create table foo (id bigint not null auto_increment, bar_id varchar(128) not null unique, primary key (id), unique (bar_id));

"show index from foo" returns (i cut the end of the line):
+-------+------------+----------+--------------+-------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
+-------+------------+----------+--------------+-------------+
| foo   |          0 | PRIMARY  |            1 | id          |
| foo   |          0 | bar_id   |            1 | bar_id      |
| foo   |          0 | bar_id_2 |            1 | bar_id      |
+-------+------------+----------+--------------+-------------+

How to repeat:
Create the table with:
create table foo (id bigint not null auto_increment, bar_id varchar(128) not null unique, primary key (id), unique (bar_id));

See the result with:
show index from foo;
[19 Jun 2008 14:18] Susanne Ebrecht
Verified as described:

create table foo (id bigint not null auto_increment, bar_id varchar(128) not null unique, primary key (id), unique (bar_id));

show create table foo\G
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `bar_id` varchar(128) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `bar_id` (`bar_id`),
  UNIQUE KEY `bar_id_2` (`bar_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

tested with 5.0 and 5.1 bzr tree.
[7 Oct 2008 21:10] Konstantin Osipov
This is a duplicate of Bug#8565
[7 Oct 2008 21:12] Konstantin Osipov
I'm sorry, this is not a duplicate.
But the behavior is intended. At best we can add a warning in this case.
Thus setting to "Verified".
[7 Oct 2008 22:10] anzenews asdf
Why is this behaviour intended? In other words, why would there ever be any need to have two identical indexes on the same table?
[7 Oct 2008 23:15] Konstantin Osipov
MySQL design principle is to allow choice, as long as there is no ambiguity.
Both indexes are explicitly requested in the CREATE specification, an thus are created.
[15 Oct 2008 3:03] anzenews asdf
Is there any situation you can think of when this would come handy? 

On the other hand it takes precious space and time (at least when updating if not with selects anymore - not sure - see bug 8565).

I would expect MySQL to perform basic checks for me and not allow obviously meaningless operations. Adding a second index is such an operation, and it is a waste of time/effort that people have to check before adding each index. 

Bug 8565 is much more vital to performance, and since they are similar, I guess solving one would solve the other one too.

But as I said there, I have solved this on application level for me... it just seems a weird point of view to deliberately allow this. :)

Enjoy!
[13 Jul 2012 6:59] Jon Olav Hauglid
Bug#8565 was marked as a duplicate of this bug.
[30 Jul 2012 17:58] Paul Dubois
Noted in 5.6.7 changelog.

The server now issues a Note diagnostic if an index is created that
duplicates an existing index. 

Noted in 5.7.0 changelog.

The server now issues a warning if an index is created that
duplicates an existing index, or an error in strict SQL mode.
[3 Jun 2013 21:39] James Day
anzenews asdf, the index pages have to be cached, say in the InnoDB buffer pool. 

Consider a case where you have one short index and one long covering index, sizes say 50 bytes and 500 bytes. If the short index is very frequently used, particularly for larger index scans, and the covering index much less used, then there could be a major performance drop from removing the shorter index. Removing it can cause many more pages to be scanned and will increase the amount of buffer pool used to hold the same number of actively used index entries.

It's good to know that MySQL can use prefix indexes but that is not always the correct solution. Just something to consider as a general rule. If the size difference is small it'll usually be a good idea. If the size difference is big it can be better to have the duplication.

A note when creating the table or adding an index can be useful but an error would be a bad idea because it would prevent adding useful duplication.

James Day, MySQL Senior Principal Support Engineer, Oracle.