MySQL Bugs: #37520: CREATE TABLE: no warning if requested duplicate index for the same column

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:		CPU Architecture:	Any
Tags:	INDEX, unique constraint

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;

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.

This is a duplicate of Bug#8565

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".

Why is this behaviour intended? In other words, why would there ever be any need to have two identical indexes on the same table?

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.

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!

Bug#8565 was marked as a duplicate of this bug.

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.

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.