MySQL supports a wide range of data types, which determine how MySQL will store your data. Any one of multiple data types can be used to store a piece of data, but which one is optimal?
What is Optimal?
For me, optimal is the one that takes the least amount space to store and/or is the fastest to search.
MySQL Data Types
MySQL supports numeric types, date and time types, and string (character) types, each of which serves a specific purpose. Some are optimal for storing small amounts of text, others large amounts of text, others dates, others numbers etc… It is best practice to understand what type of data each field in your table will be storing and choose the appropriate data type.
Incorrect Usage
The “TEXT” data type stores string data with a maximum length of 65,535 bytes, so it’s intended for storing relatively large strings. The “VARCHAR” data type stores variable-length strings with a range of 0 to 255 and is intended for storing relatively small strings. The storage requirement for “TEXT” fields is the field length plus 2 bytes, whereas “VARCHAR” fields require the field length plus one byte. So for smaller strings “VARCHAR” is optimal as opposed to “TEXT”.
When storing a number you could theoretically use “TEXT” or “VARCHAR” fields and you’d still be able to work with your data as you would expect. However, you should store the number using one of MySQL’s numeric data types. Storing a number using the optimal data type requires less storage space and your table can also be searched faster.
Examples
Numeric Data
Numbers should usually be stored using a numeric data type, most commonly “TINYINT”, “SMALLINT”, “MEDIUMINT”, “INT” and “DECIMAL”.
The first four data types store integers with varying minimum and maximum values. For example, “TINYINT” columns can store integers ranging from -128 to 127 and “INT” columns range from -2147483648 to 2147483647. “INYINT” columns require 1 byte and “INT” columns 4 bytes storage. Therefore you should use “TINYINT” to store numbers that you know will always be small.
Strings
Strings (text etc…) can also be stored using various data types. The main types that used are “CHAR”, “VARCHAR” and “TEXT”.
“TEXT” is used to store long strings, but what about short strings? Both “CHAR” and “VARCHAR” can hold between 0 and 255 bytes so how to you choose? The two data types are subtly different.
“CHAR”
A “CHAR” column’s length is fixed to the one declared when you create the table. Values are stored right-padded with spaces to the specified length. So a “CHAR(255)” column will require 255 bytes even if your data is less. But “CHAR” fields also have a speed advantage because they are fixed-length and MySQL can search them faster than variable-length fields. However, note that the whole table must be fixed-length for this to occur.
“VARCHAR”
Values in “VARCHAR” columns are variable-length strings and stored as a one-byte length prefix plus data. A “VARCHAR(255)” column with only 5 characters data would only require 6 bytes storage. Therefore “VARCHAR” is preferable for variable-length data.
Dates
Unix Timestamps
One way to store dates is as Unix timestamps, with the most optimal data type being “INT(UNSIGNED)”, requiring just 4 bytes storage.
One disadvantage is that Unix timestamps are not easily readable. And due to the 10-digit length, Unix timestamps will have problems at 3:14:07AM on 19th January 2038 when the maximum value 9999999999 is reached!
DATETIME
This data type is designed to store both the date and time as 2010-01-11 12:00:00 (for example). Although this requires 8 bytes storage, there are advantages to using “DATETIME” rather than Unix timestamps.
Firstly, “DATETIME” is human-readable.
Secondly MySQL has many built-in functions to work with this native formats. MySQL can add, subtract, format your dates, and much more.
ENUM
“ENUM” columns efficiently store data that only contain a given set of values. This is useful for setting a switch to specify whether an e-commerce product is available or not. Instead of storing ‘in-stock’ or ‘out-of-stock’ in a “VARCHAR” column, use “ENUM”.
Conclusion
Data can be stored using multiple data types, but there’s usually one that is optimal. Use of the optimal data type brings benefits in terms of storage and possibly speed, as website developers we should aspire to achieve as many performance gains as possible.
