Changing default sorting behavior of mysql

Question

I have a table in my database.

+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| rollno | int(11)     | NO   |     | NULL    |                |
| name   | varchar(20) | NO   |     | NULL    |                |
| marks  | int(11)     | NO   |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+

By default if I query

select * from students;

Shows result sorted by id INT (auto-increment).

+----+--------+------------+-------+
| id | rollno | name       | marks |
+----+--------+------------+-------+
|  1 |     65 | John Doe   |    89 |
|  2 |     62 | John Skeet |    76 |
|  3 |     33 | Mike Ross  |    78 |
+----+--------+------------+-------+
3 rows in set (0.00 sec)

I want to change default sorting behaviour and make rollno the default sorting field, how do I do this?

In my situation I need to sort it by rollno most of the time so I want if it is stored in that order by default I won't have to sort in each query. — user2009750
– user2009750, Commented Apr 2, 2014 at 16:07
What you are asking for is essentially not possible. This would require the table to be constantly resorted on each update, which any SQL engine simply will not do for obvious reasons. There is a lot more complexity in doing that than there is in simply adding an ORDER BY onto your query. — ElGavilan
– ElGavilan, Commented Apr 2, 2014 at 16:10
In my condition inserts/updates are quite less as compared to selects that's why I want to persist modified sorting. — user2009750
– user2009750, Commented Apr 2, 2014 at 16:13
There's no point to it. Mysql (nor any other DBMS) will not persist sorting on a table. You could technically run ALTER TABLE students ORDER BY rollno ASC; after every insert/update, but you would take a huge performance hit as opposed to just sorting the results of a select. Which is more expensive, sorting an entire table, or sorting only the result of a select statement? What is stopping you from just adding order by to your select statement? — ElGavilan
– ElGavilan, Commented Apr 2, 2014 at 16:16

Community · Accepted Answer · 2020-06-20 09:12:55Z

5

There is no default sort order!

The DB returns the data in the fastest way possible. If this happen to be the order in which it is stored or a key is defined then this is up to the system. You can't rely on that.

Think about it: Why would the DB use performace to order something by default if you don't need it ordered. DBs are optimised for speed.

If you want it being ordered then you have to specify that in an order by clause.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Apr 2, 2014 at 15:55

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2009750 Over a year ago

In my situation I need to sort it by rollno most of the time so I want if it is stored in that order by default I won't have to sort in each query.

juergen d Over a year ago

You don't understand. You can't rely on any default order. If you want it ordered - add an order by clause. Otherwise don't. That is the only way of ordering relyable.

mituw16 · Accepted Answer · 2014-04-02 15:56:49Z

1

Run this

ALTER TABLE students ORDER BY rollno ASC;

answered Apr 2, 2014 at 15:56

mituw16

5,2784 gold badges31 silver badges56 bronze badges

3 Comments

ElGavilan Over a year ago

This will only sort existing data in the table; it will not maintain the sort order in the table if rows are added or changed. You should never rely on the assumption that data in a table is already presorted.

user2009750 Over a year ago

This is exactly what I want but as @ElGavilan said what about future inserts/updates?

ElGavilan Over a year ago

Databases are optimized for speed not for order. It is much faster to only have to sort the result of a select than it is to consistently sort the entire contents of a table.

ElGavilan · Accepted Answer · 2014-04-02 16:02:14Z

0

select * from students order by rollno asc; will return your results sorted by that column. It should be noted that there is no default sorting behavior as far as data is actually stored in the database (aside from identities and indexes); you should never depend on your results being sorted a certain way unless you explicitly sort them (using order by).

edited Apr 2, 2014 at 16:02

answered Apr 2, 2014 at 15:56

ElGavilan

6,97216 gold badges30 silver badges36 bronze badges

Collectives™ on Stack Overflow

Changing default sorting behavior of mysql

3 Answers 3

There is no default sort order!

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

There is no default sort order!

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related