MySQL also has the unspoken rule –select statement does not add order by how to sort?

Today, there is a problem with a SELECT statement that does not have an "Order by" and the returned data is indeterminate.

This kind of problem has met more than a few times. Traced, if the SELECT statement does not add "order by", how will MySQL be sorted?

Searched the Internet and found this article in the MySQL forum.

http://forums.mysql.com/read.php?21,239471,239688#msg-239688

A simple translation

* Cannot rely on MySQL's default sort
* If you want to sort, always add order by
* GROUP by imposes an order by (this conflicts with the standard syntax, if you want to avoid it, use Order by NULL) Here I have a question,
What Order by?

For MyISAM tables
The MySQL Select default sort is displayed in physical storage order. (No additional sorting is performed).
Other words
The SELECT * from tbl– will produce a "table scan". If the table has no delete, replace, update operations, the records are displayed in the order in which they were inserted.

InnoDB table
In the same situation, they are sorted in the order of the primary key. Again, this is just the unspoken rule (artifact of the underlying implementation: how to translate? ），
Not a reliable.

My understanding and speculation:

When "Select" Does not add "order by", MySQL attempts to return data in the fastest possible way (not necessarily the actual method of MySQL).
Because access to the primary key and index is most likely to be faster (in the cache), the returned data may be output in the order of the primary key, index,
This does not really sort, mainly because the primary key, the index itself is sort of put into memory, so the continuous output may be some sort of sequence.
In some cases, the data that consumes the shortest drive seek time is returned first.
If you only query a single table, it is regular in special cases.

Final summary

"Order by is to be added"

If anyone wants to know more about it, they need to look at the source of MySQL.

MySQL also has the unspoken rule –select statement does not add order by how to sort?