Using AUTO_INCREMENT For information about  AUTO_INCREMENT  usage specific to  InnoDB , see Section 15.6.1.6, “AUTO_INCREMENT Handling in InnoDB” . In this case (when the  AUTO_INCREMENT  column is part of a multiple-column index),  AUTO_INCREMENT values are reused if you delete the row with the biggest  AUTO_INCREMENT  value in any group. This happens even for  MyISAM  tables, for which  AUTO_INCREMENT  values normally are not reused. More information about  AUTO_INCREMENT  is available here: •How to assign the  AUTO_INCREMENT  attribute to a column: Section 13.1.20, “CREATE TABLE Statement” , and Section 13.1.9, “ALTER TABLE Statement” . •How  AUTO_INCREMENT  behaves depending on the  NO_AUTO_VALUE_ON_ZERO  SQL mode: Section 5.1.11, “Server SQL Modes” . •If the  AUTO_INCREMENT  column is part of multiple indexes, MySQL generates sequence values using the index that begins with the  AUTO_INCREMENT  column, if there is one. For example, if the  animals table contained indexes  PRIMARY KEY (grp, id)  and  INDEX (id) , MySQL would ignore the PRIMARY KEY  for generating sequence values. As a result, the table would contain a single sequence, not a sequence per  grp  value. •For  MyISAM  tables, you can specify  AUTO_INCREMENT  on a secondary column in a multiple- column index. In this case, the generated value for the  AUTO_INCREMENT  column is calculated as MAX( auto_increment_column ) + 1 WHERE prefix= given-prefix . This is useful when you want to put data into ordered groups. CREATE TABLE animals ( grp ENUM(’fish’,’mammal’,’bird’) NOT NULL, id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (grp,id) ) ENGINE=MyISAM; INSERT INTO animals (grp,name) VALUES (’mammal’,’dog’),(’mammal’,’cat’), (’bird’,’penguin’),(’fish’,’lax’),(’mammal’,’whale’), (’bird’,’ostrich’); SELECT * FROM animals ORDER BY grp,id; +--------+----+---------+ | grp | id | name | +--------+----+---------+ | fish | 1 | lax | | mammal | 1 | dog | | mammal | 2 | cat | | mammal | 3 | whale | | bird | 1 | penguin | | bird | 2 | ostrich | +--------+----+---------+ Further Reading 335 Which returns: InnoDB Notes MyISAM Notes mysql>  ALTER TABLE tbl AUTO_INCREMENT = 100;