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?
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 tablewhere 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 tableand column2 is in Block 2 due to row chaining, then you would in fact see a «table fetch continued row»
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
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.
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.
The relational model has no concept of ordering of columns within rows and no concept of ordering of rows within tables.
No it shouldn't matter. A normalized database should not have constraints on the column order, as well.
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.
A "When would you you want to use this" rather than a performance issue.
Row based replication will break if the column order differs between master and slave and the column types are incompatible (Error 1677).
ALTER TABLE contacts MODIFY email VARCHAR(60) AFTER name;
would be one way to correct this problem.
I thought that it has no matter of performance, but it has in some cases - when you use indexes.
Example.
I've used join table:
| category_id | user_id |
Both columns were unsigned int (10), and primary key ( category_id, user_id )
Pay attention on order of columns. When I began to select from that table with where user_id = ? - performance was low.
After I changed order of columns (made user_id first) it began select times faster.
