Jump to content

table not being sorted by primary key by default


E_Leeder

Recommended Posts

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 by E_Leeder
Link to comment
Share on other sites

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.)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.