MySQL, ORDER BY insertion order, no sorting columns

Question

How can I order values from a table, ascending from the time they were inserted. There is no special column for this matter, like a timestamp or autoincrement.

I know this is not recommended to do... Still I would like to know how to do this.

As I understand from the answers, if no sorting columns e.g: timestamp or autoincremental were added before the values were inserted, there is no way of sorting them by insertion.

Answer 1

There is no guarantee that rows will be returned in any particular order without an ORDER BY clause in the query.

Consider a simple query that returns all columns of all rows in a table. For example:

SELECT * FROM mytable ; 

For that query, it is likely that MySQL will perform a full table scan, from the beginning of the table. So it is likely that the rows will be returned in the order they are found in physical storage.

This may roughly correspond to the order that rows were inserted, if there have been no deletes, no updates and no reorganization, where space for an inserted row was later reclaimed, and reused to store a newly inserted row.

But this behavior is NOT guaranteed.

To return the rows in the order that they were inserted, the query must specify the sequence that rows are to be returned, by including an ORDER BY clause.

For the rows to be returned in "insertion order", that means the query needs to be able to have that information available, or be able to derive that. For a simple query against a single table, that means the information needs to be stored in the row.