In some databases, it is more efficient to order the columns in a specific manner because of the way the disk access is performed. The optimal order of columns in a MySQL InnoDB table is:

  • Primary key
  • Combined primary keys as defined in the KEY order
  • Foreign keys used in JOIN queries
  • Columns with an INDEX used in WHERE conditions or ORDER BY statements
  • Others columns used in WHERE conditions
  • Others columns used in ORDER BY statements
  • VARCHAR columns with a variable length
  • Large TEXT and BLOB columns

When there are many VARCHAR columns (with variable length) in a MySQL table, the column order MAY affect the performance of queries. The less close a column is to the beginning of the row, the more preceding columns the InnoDB engine should examine to find out the offset of a given one. Columns that are closer to the beginning of the table are therefore selected faster.