Does column order matter in your MySQL tables?

user_415194 0
Viewed 10885

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?

mysql database
8 Answers

Yes, column order does matter. But, if you are looking to optimize, your most likely bet (in 90% of cases) is to add an index. The official MySQL documentation only discusses optimization in the context of adding indices (Source: Dev.MySQL.com: How MySQL Uses Indexes).

But to the question -- column order absolutely matters. It's really all a matter of how chained rows and memory blocks work within the MySQL Engine. To quote Martin Zahn, an Oracle-certified professional, in an article on The Secrets of Oracle Row Chaining and Migration...

Chained rows affect us differently. Here, it depends on the data we need. If we had a row with two columns that was spread over two blocks, the query:

SELECT column1 FROM table

where column1 is in Block 1, would not cause any «table fetch continued row». It would not actually have to get column2, it would not follow the chained row all of the way out. On the other hand, if we ask for:

SELECT column2 FROM table

and column2 is in Block 2 due to row chaining, then you would in fact see a «table fetch continued row»

Mapping a MySQL Row to a Data Block

This clearly gives us the impression that, if we select column2 more than we select column1, then we should reorder the columns as a means of optimizing our database queries.

I have found an old post on the HP Enterprise forums from 2002, which has since been recopied by at least one hundred posts after searching for it. The suggestions here on what to do for column-ordering certainly seem to match the detailed explanations of the professionals. So, almost two decades later, I gotta say it: thanks, Bill Thorsteinson!

To optimize your queries, order your columns according to these rules:

  • Primary key columns first.
  • Foreign key columns next.
  • Frequently-searched columns next.
  • Frequently-updated columns later.
  • Nullable columns last.
  • Least-used nullable columns after more-frequently used nullable columns.
  • Blobs in own table with few other columns.

Source: HP Forums

user_24305490

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.

user_2178620

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

user_13881920

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

user_235280

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.

user_1224660
Related