0

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.

Aufwind
  • 25,310
  • 38
  • 109
  • 154

1 Answers1

4

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.

gbn
  • 422,506
  • 82
  • 585
  • 676
  • I need to make this column useful for another task I intend to accomplish where gaps are a no go. Thanks for the answer, though! – Aufwind Aug 28 '11 at 14:16
  • I googled `ROW_NUMBER` emulation and the results were confusing. So just to be sure: Do you mean to just identify the rows by an additional column which contains all three indexes of all three columns? If yes, what do you mean by `ROW_NUMBER` emulation? – Aufwind Aug 28 '11 at 14:29
  • @Aufwind: Other RDBMS have ROW_NUMBER() OVER () to generate sequential numbers eg http://msdn.microsoft.com/en-us/library/ms186734.aspx MySQL doesn't but can be worked around: http://stackoverflow.com/questions/1895110/row-number-in-mysql It depends if the sequential numbers need to be the same from call to call. If not, use ROW_NUMBER replacement. If yes, then you can't do it with Autonumber. FYI: autonumber/identity values should have no intrinsic meaning anyway. They are simply internal surrogate keys – gbn Aug 28 '11 at 14:33