Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-2837

LP #1081003: mysqldump --innodb-optimize-keys handles AUTO_INCREMENT columns inefficiently

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: Low
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      Description

      **Reported in Launchpad by Alexey Kopytov last update 13-12-2012 01:03:52

      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.

        Smart Checklist

          Attachments

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              lpjirasync lpjirasync (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: