Percona Server with XtraDB

mysqldump --innodb-optimize-keys handles AUTO_INCREMENT columns inefficiently

Reported by Alexey Kopytov on 2012-11-20
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Server
Undecided
Alexey Kopytov
5.1
Undecided
Alexey Kopytov
5.5
Undecided
Alexey Kopytov

Bug Description

When mysqldump --innodb-optimize-keys encounters an AUTO_INCREMENT column in SHOW CREATE TABLE, it correctly marks that column, so that when subsequent key specifications involving that column are not optimized away, as MySQL requires AUTO_INCREMENT columns to be indexed, and removing them from CREATE TABLE would result in an invalid SQL.

However, it avoids optimizing all keys indexing that column from , even though it would be sufficient to avoid optimization just for the first one.

For example: the following table definition:

CREATE TABLE t2 (
       id INT NOT NULL AUTO_INCREMENT,
       uid INT NOT NULL,
       PRIMARY KEY (id),
       KEY k1 (id),
       KEY k2 (id)
) ENGINE=InnoDB;

results in the following output by mysqldump --innodb-optimize-keys:

DROP TABLE IF EXISTS `t2`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `k1` (`id`),
  KEY `k2` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

LOCK TABLES `t2` WRITE;
/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
UNLOCK TABLES;

Obviously, creating the table with just the primary key would be sufficient, and keys 'k1' and 'k2' can be created after loading the data.

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers