It seems that a
SELECT is not guaranteed to return rows in any specific order (without using an
ORDER BY clause, of course).
As per the SQL-92 standard (p. 373):
If an < order by clause > is not specified, then the table specified by the < cursor specification > is T and the ordering of rows in T is implementation-dependent.
Okay, MySQL is not fully SQL-92-compliant, but this is a serious hint.
Laurynas Biveinis (apparently affiliated with Percona) also states:
The order of the rows in the absence of
ORDER BY clause (...) could be different due to the moon phase and that is OK.
The MySQL manual says about InnoDB:
InnoDB always orders table rows according to [a
PRIMARY KEY or
NOT NULL UNIQUE index] if one is present.
As far as I am concerned, I assume MySQL could also reorder rows after an
OPTIMIZE TABLE or even reuse empty spaces after many deletes and inserts (I have tried to find an example of this, and have failed so far).
Given your table structure, the bottomline is, unfortunately, that so many factors could have altered the order of the rows; I see no solution to reliably determine the order they were inserted. Unless you kept all binary logs since you created the table, of course ;)
Nevertheless, you may still want to add a
sequence column to your table. Existing rows would be assigned a possibly inaccurate sequence number, but at least future rows will be correctly sequenced.
ALTER TABLE maxID ADD sequence INT DEFAULT NULL;
ALTER TABLE maxID ADD INDEX(sequence);
ALTER TABLE maxID MODIFY sequence INT AUTO_INCREMENT;