Mysql: Order of results with "select <fieldname> from" is different to order of results with "select * from"

Question

Hi all. i have a mysql db with a table 'difficulties' with a few records. If i do "select * from difficulties" i get them back in the order they were added, ordered by primary key id:

mysql> select * from difficulties;
+----+-------+-----------+--------+----------+-----------+
| id | value | name      | letter | low_band | high_band |
+----+-------+-----------+--------+----------+-----------+
|  1 |     1 | very_easy | VE     |        1 |         1 | 
|  2 |     2 | easy      | E      |        2 |         5 | 
|  3 |     3 | medium    | M      |        6 |        10 | 
|  4 |     4 | hard      | H      |       11 |        12 | 
|  5 |     0 | na        | NA     |        0 |         0 | 
+----+-------+-----------+--------+----------+-----------+

However, if i do "select name from difficulties" i get them back in a different order:

mysql> select name from difficulties;
+-----------+
| name      |
+-----------+
| easy      | 
| hard      | 
| medium    | 
| na        | 
| very_easy | 
+-----------+

My question is: what determines this order? Is there any logic to it? Is it something like "the order the files representing the records happen to be in within the filesystem" or something else that is to all intents and purposes random?

thanks, max

Answer 1

This is correct and by design: if you don't ask for sorting, the server doesn't bother with sorting (sorting can be an expensive operation), and it will return the rows in whatever order it sees fit. Without a requested order, the way the records are ordered can even differ from one query to the next (although that's not too likely).

The order is definitely not random - it's just whatever way the rows come out of the query, and as you see, even minor modifications can change this un-order significantly. This "undefined" ordering is implementation dependent, unpredictable and should not be relied upon.

If you want the elements to be ordered, use the ORDER BY clause (that's its purpose) - e.g.

SELECT name FROM difficulties ORDER BY name ASC;

That will always return the result sorted by name, in ascending order. Or, if you want them ordered by the primary key, last on top, use:

SELECT name FROM difficulties ORDER BY id DESC;

You can even sort by function - if you actually want random order, do this (caveat: horrible performance with largish tables):

SELECT name FROM difficulties ORDER BY RAND();

For more details see this tutorial and the documentation.

Answer 2

select name from difficulties should return the values in alphabetical order as it is a text field. And select * from difficulties will return in numeric order i believe. dont hold me to that lol

best thing to do is use ORDER BY if you care about what order things are

Answer 3

As Piskvor said, MySQL will order the query however it finds most convenient. To address the "why" part of your question, the different result orders are probably a side effect of different execution plans. If you have an index on difficulties, the second query would make use of it but the first would not.

Answer 4

Without the ORDER BY clause, the results are returned in random order. However, it seems logical to me that the easiest (and the fastest) way for db engine to return data as it's stored. So it's why the fist resultset is ordered by PK (no fragmentation, logical order is the same as physical). In the second case I would assume that you have an index on field name, and for the query select name from difficulties this index is covering, so db engine scans this index, and it's why you see results ordered by name. Anyway, you shouldn't rely on such "default" ordering.