Is this true? performance question

Question

I heard somewhere that you should have your int type fields before all other types in your tables. They said the query runs faster or something. Is this true?

For example -

• id int(10)
• time int(11)
• user_id int(10)
• title varchar(128)
• text text

...instead of:

• id int(10)
• title varchar(128)
• text text
• time int(11)
• user_id int(10)

Answer 1

Any difference will be negligible. Order your columns so that the most important columns come first, and the longest (string) columns come last (just for readability): keys, then foreign keys, then numeric attributes, then strings.

Answer 2

I'm sure this will not make much difference, but you could benchmark a large data set to try it out. I searched the excellent MySQL Performance Blog for references without finding anything. The MySQL manual section on optimization has this to say, which is only marginally related

For MyISAM tables, if you do not have any variable-length columns (VARCHAR, TEXT, or BLOB columns), a fixed-size row format is used. This is faster but unfortunately may waste some space. See Section 13.1.3, “MyISAM Table Storage Formats”. You can hint that you want to have fixed length rows even if you have VARCHAR columns with the CREATE TABLE option ROW_FORMAT=FIXED.

Answer 3

Without knowing specifics of MySQL, assuming text type is added to main data table as is instead of a reference to some other internal data table and that the underlying RDBMS doesn't internally change the order of columns to most optimal one then yes, that would cause performance issues.

The reason is that databases generally store the rows in table as a set of bytes which are aligned based on datatypes. That means that since the DB knows that the table datatypes are for example id (x bits)something(y bits)+date(z bits), selecting all id:s would be just as simple as selecting 0...x bitz and then incrementing offset with y+z bits until the DB hits the end of the actual table file.

However if you include a variable-length column in the middle of that, additional calculations for next offset must be done on every single row and this may cause performance issues especially if you have large datasets (=lots of rows) in a single table.

Answer 4

This seems very unlikely as if it was an issue the first optimisation the database would do is to store the columns in the most efficient order regardless of what order they were defined in.

Maybe, if your English isn't good, the reference was to do with using int in indexes? For example, you should use an int as your primary key rather than a string (such as a GUID) as int uses hash tables more efficiently than using random GUIDs or other strings will. Also, if you can, you should put int columns first in your index definition because there the order does make a difference and using int before string will be more efficient (assuming your index is still useful in this order for your application needs).