MySQL, ORDER BY insertion order, no sorting columns

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.

You can ORDER BY something you can get out of your table. If you do not have anything in there that can be used to find out the order you need, you cannot order by it.

Depending on the data in the table, you may be able to order by the id of the data - if the data has a single incremental integer to assure PK uniqueness. There is no other way to sort on insertion order unless the data is captured and recorded in the table.

I don't know of anything in MySQL that retains extra (meta) information on records that you have not specified at the table level.