10

I've heard (from a colleague, who heard it from another developer) that VARCHAR columns should always be put at the end of a table definition in MySQL, because they are variable in length and could therefore slow down queries.

The research I've done on stack overflow seems to contradict this however and suggests that column order is important, while there is varying agreement on how much this applies to VARCHARs.

He wasn't specific about storage engines, or about whether this only applied to VARCHAR columns which are infrequently accessed.

ProcessEight
  • 399
  • 1
  • 2
  • 9
  • possible duplicate of [Does the order of columns in the table matter?](http://stackoverflow.com/questions/4453429/does-the-order-of-columns-in-the-table-matter) – Denis de Bernardy May 26 '11 at 13:54
  • possible duplicate of [mysql column order for varchar and ints](http://stackoverflow.com/questions/4538993/mysql-column-order-for-varchar-and-ints) – Lukas Eder May 26 '11 at 13:59
  • i could imagine a scenario where the fear would be that a small column like a number would be at the 'end' of the record, and then that record somehow becomes chained due to an update of a varchar, then that number would be on the 'chained' part of the row for retrieval... i have no way to prove it, but i think most engines would optimize this storage. – Randy May 26 '11 at 14:06

2 Answers2

11

Asking that question about "MySQL" is not helpful, as MySQL relegates storage to storage engines, and they implement storage in very different ways. It makes sense to ask this question for any individual storage engine.

In the MEMORY engine, variable length data types do not exist. A VARCHAR is silently changed into a CHAR. In the context of your question: It does not matter where in a table definition you put your VARCHAR.

In the MyISAM engine, if a table has no variable length data whatsoever (VARCHAR, VARBINARY or any TEXT or BLOB type) it is of the FIXED variant of MyISAM, that is, records have a fixed byte length. This can have performance implications, especially if data is deleted and inserted repeatedly (i.e. the table is not append only). As soon as any variable length data type is part of a table definition it becomes the DYNAMIC variant of MyISAM, and MyISAM internally changes any but the shortest CHAR type internally to VARCHAR. Again, position and even definition of CHAR/VARCHAR do not matter.

In the InnoDB engine, data is stored in pages of 16 KB size. A page has a page footer with a checksum, and a page header, with among other things a page directory. The page directory contains for each row the offset of that row relative to the beginning of the page. A page also contains free space, and all I/O is done in pages.

Hence InnoDB can, as long as there is free space in a page, grow VARCHAR in place, and move rows around inside a page, without incurring any additional I/O. Also, since all rows are being addressed as (pagenumber, page directory entry), movement of a row inside a page is localized to the page and not visible from the outside.

It also means that for InnoDB too, the order of columns inside a row does not matter at all.

These are the three storage engines that are most commonly used with MySQL, and order of columns does not matter for any of these three. It may be that other, more exotic storage engines exist for which this is not true.

Isotopp
  • 3,313
  • 1
  • 16
  • 17
3

It does not matter. And some engines store varlena types in a separate area (e.g. TOAST in Postgres).

Moreover, the logical order (what you see when you select *) may actually differ from the physical order (how it's stored, which is based on the order in which you've created the actual columns using subsequent alter table statements).

http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Anatomy-of-a-record.aspx

Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154