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

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.

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.

Update: in practice, InnoDB returns rows by default in the order it reads them from the index, so the order depends on which index is used by the optimizer. Depending on the columns and conditions you have in your query, it may choose a different index.

Here's a demonstration using InnoDB: I create a table and insert rows such that the values I insert are the opposite order of the primary key.

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

INSERT INTO foo (bar, baz) VALUES
  ('test5', 'test5'), ('test5', 'test5'),
  ('test4', 'test4'), ('test4', 'test4'), 
  ('test3', 'test3'), ('test3', 'test3'), 
  ('test2', 'test2'), ('test2', 'test2'), 
  ('test1', 'test1'), ('test1', 'test1');

By default, if no index is used, the rows are returned in primary key order, because the are read from the clustered index (the primary key).

select * from foo;
+----+-------+-------+
| id | bar   | baz   |
+----+-------+-------+
|  1 | test5 | test5 |
|  2 | test5 | test5 |
|  3 | test4 | test4 |
|  4 | test4 | test4 |
|  5 | test3 | test3 |
....

But if we use a query that uses an index, it reads the rows in the order of that index. Notice when there are ties, the tied index entries are stored in order of primary key, ascending. That's the order they are returned.

select * from foo where bar between 'test2' and 'test4';
+----+-------+-------+
| id | bar   | baz   |
+----+-------+-------+
|  7 | test2 | test2 |
|  8 | test2 | test2 |
|  5 | test3 | test3 |
|  6 | test3 | test3 |
|  3 | test4 | test4 |
|  4 | test4 | test4 |
+----+-------+-------+

Using a different storage engine means a different implementation, and the default order may be different. In the case of MyISAM, rows are stored in the order they were created.

Here's a demonstration of what I mean:

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.

Per this thread, default sort is insert order for MyISAM, and primary key ascending for InnoDB. But I don't think that's a guarantee, just how it's known to work.

No, you cannot.

Sometimes MySQL will perform select queries with keys you don't expect. Consider this table:

CREATE TABLE `user_permissions` (
  `permId` int(5) unsigned NOT NULL AUTO_INCREMENT,
  `permKey` varchar(16) NOT NULL,
  `permDesc` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`permId`),
  KEY `key_lookup` (`permKey`,`permId`,`permDesc`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

MySQL will almost always use the key_lookup key to do any select operation on this table; since permKey is the first field, it will usually end up "sorted" by this key (which appears alphabetical, but isn't exactly).

Without an ORDER BY clause, MySQL (and most/all RDBMS engines) will try to get the data just as it's stored and just as fast as possible.

From Retrieving Data Using the MySQL SELECT Statement : The SELECT Statement

The data displayed is not ordered. Usually records are retrieved in the same order in which they were inserted into the database

Yes, as per the comment

Although records are normally retrieved in the order in which they are inserted into the database, you cannot rely on a particular order being preserved. If your database is backed up and restored, or if a maintenance operation is performed on the database, MySQL might alter the order in which records are stored internally.

No, definitely not. Depending on the database engine you're using (ISAM or InnoDB), table structure will generally be some kind of b-tree to allow for faster searching through rows. This will have nothing to do with insertion order, and more to do with how the database constructs an index based on the primary key (or in the case of no key, how a table heap is stored).

the results in mysql 5.6 are not select by default in inserting order, you can do updates etc and it returs the query in any order it likes