Understanding MySQLs behaviour when adding a autoincremented primary key afterwards

Let's say we have a (InnoDB) table associations in a MySQL-Database which has the following structure:

CREATE TABLE `associations` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
  `fk_id_1` int(11) NOT NULL,
  `fk_id_2` int(11) NOT NULL,
  `fk_id_3` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
  UNIQUE KEY `some_unique_constraint` (`fk_id_1`,`fk_id_2`),
  KEY `fk_id_2_INDEX` (`fk_id_2`),
  KEY `fk_id_3_INDEX` (`fk_id_3`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin$$

There are jumps in the column id (I know this is开发者_运维技巧 an issue of how the autoincremented value is generated while multiple threads try to get one). Since no other table is using the column id as a reference I plan to drop the column id and to create it again, hopefully the counting holes will be gone. I backed up my database and tested that. The result was a little confusing. The order of the rows seemed to have changed. If I am not mistaken the order is first by fk_id_1 then fk_id_2 then fk_id_3.

Is this the natural order in which MySQL sets the table, when assignung an new generated autoincrement key to the rows?

Is there more I should know, that happened during this process?

The reason, why I need to know about this is that I need to make the column id useful for another task I intend to accomplish where gaps are a no go.

---

There is no natural order to a table in any mainstream RDBS.

Only the outermost ORDER BY in a SELECT statement will guarantee the order of results.

If you want "order":

• create a new table
• INSERT..SELECT..ORDER BY fk_id_1, fk_id_2, fk_id_3
• Drop old table
• Rename new table

Or live with gaps... OCD isn't good for developers

Edit:

Question says "no dependency" on this value but turns out there is.

If gaps are not allowed then don't use autonumber and use fk_id_1, fk_id_2, fk_id_3 as your key, with a ROW_NUMBER emulation. Or code your downstream to deal with gaps.

Autonumbers will have gaps: immutable fact of life.