Does column order matter in your MySQL tables?

Question

While learning mysql, I read that you can perform the following statement when adding a column to a mysql table:

ALTER TABLE contacts ADD email VARCHAR(60) AFTER name;

or

ALTER TABLE contacts ADD email VARCHAR(60) FIRST;

When would you want to do this? Can column order be utilized for query optimization purposes? Should longblobs be the last column to optimize space consumption? Or do these commands exist for some other reason?

Answer 1

No it shouldn't matter. A normalized database should not have constraints on the column order, as well.

Answer 2

This will however impact the order of the result in select * from mytable.

This is why you should always name the column in the select statement, e.g. select col1, col2 from mytable. But if you know that the app is using *, then you must take care when you add a column.

Otherwise, order the column so that it's the most logical to understand. If it affects the perf, then it means you are already on the dark side of database performance tuning and you have probably a problem somewhere else.

Answer 3

column order does not matter. This is purely a convenience feature. just to allow you to restructure your database table the way you like after it has been created.

Answer 4

The relational model has no concept of ordering of columns within rows and no concept of ordering of rows within tables.