Auto_increment Indexing

It’s so common to set the auto_increment column to primary key, it has become common belief that it’s required. Not true; an auto_increment column can use any index type. It doesn’t even have to be unique:

CREATE TABLE testTable (
  id INT AUTO_INCREMENT,
  INDEX(id)
) ENGINE=MyISAM; -- Works for InnoDB too.

INSERT INTO testTable VALUES (1), (1), (NULL);

SELECT * FROM testTable;

+----+
| id |
+----+
|  1 |
|  1 |
|  2 |
+----+