CODEWITHSUNDEEP
mysqlphpmyadminsqldatatypesmysql-5.6
oldboy
oldboy

Reputation: 5954

Most Efficient Data Type for Fixed Length Column

I'm just in the process of designing my database structures.

There are a number of columns that are fixed length, at least one of which is purely alphanumeric.

Hence I'm wondering:

  1. What is (or are) the most efficient data type(s) for fixed length columns in general?
  2. What is (or are) the most efficient data type(s) for fixed length alphanumeric columns?
  3. Why?

Upvotes: 0

Views: 1426

Answers (2)

Rick James
Rick James

Reputation: 142208

Short answer: As tadman says: 'Use VARCHAR and don't worry about it'

Long answer:

The space consumed by the column is the main factor for both space and speed.

Truly fixed-length strings can be declared CHAR(..). Very often, they are composed only of ascii characters, so the 'right' way to do it is, for example

country_code CHAR(2) CHARACTER SET ascii
uuid CHAR(36) CHARACTER SET ascii

The difference between a fixed length VARCHAR instead of CHAR is the 1- or 2-byte length field preceding the actual string. Unless you have billions of rows, this is 'not a big deal'.

The processing speed difference is insignificant, but CHAR and ascii win.

If you have all-numeric strings, you may want to use INT 4 bytes or BIGINT 8 bytes or DECIMAL(30) 14 bytes, etc -- instead of using CHAR or VARCHAR, which will have 1 byte per digit. The numeric fields are all fixed length. But, be careful. US phone numbers are fixed length, but international numbers vary.

You imply that there is something other than "alphanumeric". If you are referring to BINARY/VARBINARY/BLOB, then the rules are mostly the same.

For example, a uuid can be shrunk from CHAR(36) (36 bytes) to BINARY(16) (16 bytes) via a suitable transformation. There latter is better for speed and space, but it adds complexity to your code. (Anyway, uuids are terrible for huge table; this is another topic.)

With integers, always consider BIGINT vs INT vs MEDIUMINT vs SMALLINT vs TINYINT, and usually tack on UNSIGNED. (Those take 8/4/3/2/1 bytes, respectively.) Do it when you originally create the table; it is messy to do an ALTER later.

Upvotes: 1

tadman
tadman

Reputation: 211540

Use VARCHAR and don't worry about it.

This is only a concern when you're handling so much data it won't fit on a single drive, or even then, a single server jammed full of drives.

Companies that manage databases with multiple billions of rows have problems with this, but you won't until you're that big.

Upvotes: 1

Related Questions