Why does adding AUTO_INCREMENT take so long?

Say I have a table

CREATE TABLE `tab_sample` (
  `id` bigint(20) NOT NULL,
  `something` char(2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I would like to turn it into

CREATE TABLE `tab_sample` (
  `id` bigint(20) NOT NULL,
  `something` char(2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

But

ALTER TABLE `tab_sample` MODIFY
  id bigint(20) NOT NULL AUTO_INCREMENT;

takes forever if the table is big, because for some reason, MYSQL decides to have to rewrite the whole thing (copying to temp table first, ...).

Is there a better way to do it? In the end, this should really just change a rule for the default value of id, shouldn't it?

Solution

You're expecting the change to be a metadata-only change, but it won't be.

You can test this by requesting the alter be done as an inplace change, and seeing the error when the request cannot be satisfied.

mysql> alter table tab_sample modify id bigint not null auto_increment, algorithm=inplace;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

InnoDB has a limited subset of types of ALTER TABLE that can be done as inplace or metadata-only changes. See https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html

The alter you are requesting probably counts as "Changing the column data type" even though you're not changing the type but only changing the AUTO_INCREMENT option.

I think MySQL must do a table-copy for the edge case when your id column contains NULL or 0. In that case, MySQL will generate a new auto-increment value and replace the NULL or 0.

mysql> insert into tab_sample (id) values (10),(20),(30),(0)

mysql> ALTER TABLE `tab_sample` MODIFY   id bigint(20) NOT NULL AUTO_INCREMENT;
Query OK, 4 rows affected (0.06 sec)

mysql> select * from tab_sample;
+----+-----------+
| id | something |
+----+-----------+
|  1 | NULL      |
| 10 | NULL      |
| 20 | NULL      |
| 30 | NULL      |
+----+-----------+

See my 0 got changed to a 1 by the auto-increment.

Other edge case: the column may not have been a unique key, so it may have contained many rows with 0 or NULL, and they all would be given new id's.

mysql> create table tab_sample2 ( id bigint, key(id));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into tab_sample2 values (10),(20),(30),(0),(0),(0),(NULL),(NULL),(NULL);
Query OK, 9 rows affected (0.02 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> alter table tab_sample2 modify column id bigint auto_increment;
Query OK, 9 rows affected (0.06 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> select * from tab_sample2;
+----+
| id |
+----+
| 10 |
| 20 |
| 30 |
| 31 |
| 32 |
| 33 |
| 34 |
| 35 |
| 36 |
+----+

MySQL doesn't know how many rows in the table will need new id values. It could be every row in the table. So to be safe, it will just do a table-copy and fill in auto-inc values on as many rows as needed.