Bug #48366 data type serial creates two indexes
Submitted: 28 Oct 2009 9:07 Modified: 11 Aug 2010 17:13
Reporter: Susanne Ebrecht Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:5.1 OS:Any
Assigned to: CPU Architecture:Any

[28 Oct 2009 9:07] Susanne Ebrecht 
Description:
CREATE TABLE t(i serial, j integer, primary key(i));

SHOW CREATE TABLE t\G

       Table: t
Create Table: CREATE TABLE `t` (
  `i` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `j` int(11) DEFAULT NULL,
  PRIMARY KEY (`i`),
  UNIQUE KEY `i` (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

As you see MySQL created two unique indexes for column i.

MySQL should check here if user set PK and then only should use the PK as index and remove the other unique index.

The minimum what should happen here is to make a warning.

Two indexes instead of one will occur immense more space that the user needs. Also it could confuse the optimizer.

How to repeat:
See above

Suggested fix:
check on data type serial if pk is set or not and don't set two unique indexes here.
[11 Aug 2010 17:13] MySQL Verification Team 
Duplicate of Bug 37130