I understand you are able to Affect the column order in MySQL with FIRST and AFTER, why would you need to bother? Since good queries clearly title posts when placing data, can there be really any reason of looking after what order your posts have been in within the table?

Column order were built with a large performance effect on a few of the databases I have updated, spanning Sql Server, Oracle, and MySQL. This publish has good guidelines:

  • Primary key posts first
  • Foreign key posts next.
  • Frequently looked posts next
  • Frequently up-to-date posts later
  • Nullable posts last.
  • Least used nullable posts after more often used nullable posts

A good example for difference in performance is definitely an Index research. The database engine finds a row according to some conditions within the index, and will get back a row address. Now say you're searching for SomeValue, and it is within this table:

 SomeId int,

 SomeString varchar(100),

 SomeValue int

The engine needs to guess where SomeValue begins, because SomeString comes with an unknown length. However, should you alter the order to:

 SomeId int,

 SomeValue int,

 SomeString varchar(100)

The engine recognizes that SomeValue are available 4 bytes after the beginning of the row. So column order may have a considerable performance impact.

EDIT: Sql Server 2005 stores fixed-length fields at the beginning of the row. And every row includes a mention of the the beginning of a varchar. This completely negates the result I have in the above list. So for recent databases, column order no more has any impact.

Update:

In MySQL, there might be grounds to get this done.

Since variable datatypes (like VARCHAR) are saved with variable measures in InnoDB, the database engine should traverse all previous posts in each row to discover the offset from the given one.

The impact might be as large as 17% for 20 posts.

Check this out entry during my blog for additional detail:

In Oracle, trailing NULL posts consume no space, that's why you need to always place them towards the finish on the table.

Also in Oracle as well as in SQL Server, just in case of a big row, a ROW CHAINING may occur.

ROW CHANING is splitting a row that does not squeeze into one block and spanning it within the multiple blocks, of a linked list.

Reading through trailing posts that did not squeeze into the very first block will need crossing the linked list, resulting within an extra I/O operation.

See this site for instance of ROW CHAINING in Oracle:

That's why you need to put posts you frequently use to the start of the table, and posts you do not use frequently, or posts that are usually NULL, towards the finish on the table.

Important note:

If you want this answer and wish to election for this, please also election for @Andomar's answer.

He clarified exactly the same factor, but appears to become downvoted for pointless.