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

The question has nothing to do with the relational model or SQL. It is a performance question.

In some databases, it is more efficient to order the columns in a specific manner because of the way the disk access is performed. Whether there is significant advantage is platform specific, as well. It is a low-level i/o issue related to the way the underlying storage is designed and the way it is accessed by the engine. Proprietary engine providers generally provide this information via their education and training departments.

I think you would have to talk to someone who knows the nitty gritty details of the storage model and i/o methods for MySQL on your specific platform or someone who has bench-marked this on your platform in order to get an answer.

It's entirely possible they lay it down on disk in an optimized manner and hide that column ordering from you.

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.