A Little Noise

31Aug/061

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 |
+----+
Filed under: MySQL FAQ Leave a comment
Comments (1) Trackbacks (0)
  1. Hi!

    The funny thing is that nothing in the code requires that we have an index on autoincrements, this is just the way it happens to be setup.

    Cheers,

    -Brian


Leave a comment

No trackbacks yet.