table not being sorted by primary key by default

[E_Leeder]

I have a table with two columns. The first is an id column, set to be my primary key, non null, unique, int, and auto increment. The second is a varchar(10) column. Very strangely, when I set the varchar(10) column to unique, and try to edit the table in workbench, the table is automatically ordered by my varchar column, and not my id (primary key column). If I mark the varchar(10) column as not unique, data is ordered by the primary key, as expected. What is going on here? I expected the primary key to be default ordering for the table. I hope to not use an ORDER BY clause.

Edited November 29, 2014 by E_Leeder

[mac_gyver]

there is NO default sorting on database tables. the ordering in a result set is the order that the rows have in the table. this is typically the order in which they were inserted, but as you delete, insert, update, index, backup/restore, ... the data, the order of the rows can get changed.

 

if you want data retrieved in a specific known order, you must use an ORDER BY term in your query (note: a GROUP BY also performs an ORDER BY since the rows must be sorted before they can be grouped, but you can apply your own ORDER BY after the GROUP BY to order the resulting groups the way you want them to be retrieved.)

[Barand]

You may want to read up on clustered indexes

http://lmgtfy.com/?q=mysql+clustered+index

[E_Leeder]

I've never heard of clustered indexes, thanks for sharing.

 

Just considering mysql workbench here (although it may be applicable outside of it; I haven't tested yet)...

I know the row order isn't explicit in mysql or relation databases in general, but it's strange that if I mark a varchar column as unique, the default sorting is by the varchar column, not the primary key. Strange because it IS sorting by the varchar column at all, when it should be defaulting to the insert order or the primary key. Maybe it is just a workbench issue?

 

I did more testing today and discovered that if I add another varchar column, and mark it as unique, (and just leave it full of nulls) the default sorting reverts to sorting by the auto-incrementing primary key column (which maps to insert order). So something is defaulting to prefer ordering by the first varchar(20) column when there is only a varchar(20) and my AI,NN, UN, PK column, but that default sorting changes if another varchar column is added.

[kicken]

Whether the column is unique or not probably affects how mysql finds and retrieves the rows. Without the unique column it probably just does a table scan, thus you get them in the table order. With the unique index it probably uses that index which will be ordered to provide quick searching.

 

That's just speculation. It doesn't really matter why it does what it does though. If you don't specify a ORDER BY clause then any ordering of the results is just as valid as any other ordering.