If you delete the row containing the maximum value for an
AUTO_INCREMENT column, the value is not reused for a MyISAM or InnoDB
table. If you delete all rows in the table with DELETE FROM tbl_name
(without a WHERE clause) in autocommit mode, the sequence starts over
for all storage engines except InnoDB and MyISAM. There are some
exceptions to this behavior for InnoDB tables, as discussed in Section
13.3.5.3, “AUTO_INCREMENT Handling in InnoDB”.
For MyISAM tables, you can specify an AUTO_INCREMENT secondary column in a multiple-column key. In this case, reuse of values deleted from the top of the sequence occurs even for MyISAM tables. See Section 3.6.9, “Using AUTO_INCREMENT”.