What is MySQL row order for "SELECT * FROM table_name;"?

Question

Assume that the following query is issued to a MySQL database:

SELECT * FROM table_name;

Note that no ORDER BY clause is given.

My question is:

Does MySQL give any guarantees to which order the result set rows will be given?

More specifically, can I assume that the rows will be returned in insertion order?, that is the same order in which the rows were inserted into the table.

Answer 1

No, there are no guarantees. Unless you specify an order using an ORDER BY clause, the order is totally dependent on internal implementation details. I.e. whatever is most convenient for the RDBMS engine.

In practice, the rows might be returned in their original insertion order (or more accurately the order the rows exist in physical storage), but you should not depend on this. If you port your app to another brand of RDBMS, or even if you upgrade to a newer version of MySQL that may implement storage differently, the rows could come back in some other order.

The latter point is true for any SQL-compliant RDBMS.

---

Here's a demonstration of what I mean by the order the rows exist in storage, versus the order they were created:

CREATE TABLE foo (id SERIAL PRIMARY KEY, bar CHAR(10));

-- create rows with id 1 through 10
INSERT INTO foo (bar) VALUES
  ('testing'), ('testing'), ('testing'), ('testing'), ('testing'), 
  ('testing'), ('testing'), ('testing'), ('testing'), ('testing');

DELETE FROM foo WHERE id BETWEEN 4 AND 7;

+----+---------+
| id | bar     |
+----+---------+
|  1 | testing |
|  2 | testing |
|  3 | testing |
|  8 | testing |
|  9 | testing |
| 10 | testing |
+----+---------+

So now we have six rows. The storage at this point contains a gap between rows 3 and 8, left after deleting the middle rows. Deleting rows does not defragment these gaps.

-- create rows with id 11 through 20 
INSERT INTO foo (bar) VALUES
  ('testing'), ('testing'), ('testing'), ('testing'), ('testing'), 
  ('testing'), ('testing'), ('testing'), ('testing'), ('testing');

SELECT * FROM foo;

+----+---------+
| id | bar     |
+----+---------+
|  1 | testing |
|  2 | testing |
|  3 | testing |
| 14 | testing |
| 13 | testing |
| 12 | testing |
| 11 | testing |
|  8 | testing |
|  9 | testing |
| 10 | testing |
| 15 | testing |
| 16 | testing |
| 17 | testing |
| 18 | testing |
| 19 | testing |
| 20 | testing |
+----+---------+

Notice how MySQL has re-used the spaces opened by deleting rows, before appending new rows to the end of the table. Also notice that rows 11 through 14 were inserted in these spaces in reverse order, filling from the end backwards.

Therefore the order the rows are stored is not exactly the order in which they were inserted.

---

UPDATE: This demonstration I wrote in 2009 was for MyISAM. InnoDB returns rows in index order unless you use ORDER BY. This is further evidence of the point at the start of the answer that the default order depends on the implementation. Using a different storage engine means a different implementation.