When selecting everything, no longer orders by ID

[Eiolon]

I recently upgraded to the latest MySQL version.  I noticed when I run a query such as SELECT * FROM table it no longers order by the auto_incrementing ID ASC by default.  I have to tell it to do that.  Instead, it orders alphabetically by the next field.  Is there a way I can change the config to order by ASC by default when selecting everything?

[mikosiko]

You can't "trust" the order when ORDER BY is not specified, you must specify the ORDER BY if you want a specific order.

 

depending on the DB storage engine used to store the table you can get completely different results... in Innodb per example the order could be the PRIMARY KEY.

In MyIsam the order could be the Insertion order if the table never have had updates/deletes/replaces... otherwise order will be anything.

 

Is not setup available to define the default order in any storage engine AFAIK.

 

if you updated your db recently and you are seeing different order than before probably the storage engine is now Myisam instead of Innodb for the affected tables.

 

the rule of thumb: Never trust in "default order" because there is not such reliable thing... always use and ORDER BY if you want a specific order.

 

just forgot to mention that in MyIsam an option exist (but I personally never use/trust on) which is:

ALTER TABLE ORDER BY <column name>

but it is used only for creation... order is not maintained after updates/deletes/refresh

[Eiolon]

Interesting, thanks for the info!