How to choose MySQL Optimal Data Types

By:    Updated: February 28,2017

MySQL supports a large variety of data types, and choosing the correct type to store our data is crucial to getting good performance. The following simple guidelines can help us make better choices, no matter what type of data we are storing:

Smaller is usually better

In general, try to use the smallest data type that can correctly store and represent our data. Smaller data types are usually faster, because they use less space on the disk, in memory, and in the CPU cache. They also generally require fewer CPU cycles to process. If you're in doubt as to which is the best data type to use, choose the smallest one that you don't think you'll exceed. (If the system is not very busy or doesn't store much data, or if you're at an early phase in the design process, you can change it easily later.)

Simple is good

Fewer CPU cycles are typically required to process operations on simpler data types. For example, integers are cheaper to compare than characters, because character sets and collations (sorting rules) make character comparisons complicated.

Avoid NULL if possible

It's harder for MySQL to optimize queries that refer to nullable columns, because they make indexes, index statistics, and value comparisons more complicated. A nullable column uses more storage space and requires special processing inside MySQL. When a nullable column is indexed, it requires an extra byte per entry and can even cause a fixed-size index (such as an index on a single integer column) to be converted to a variable-sized one in MyISAM.

So we can use the following way to emulate NULL values:

... not null default '',
...
...unsigned not null default 0,

 

The first step in deciding what data type to use for a given column is to determine what general class of types is appropriate: numeric, string, temporal, and so on. This is usually pretty straightforward, but we mention some special cases where the choice is unintuitive.

 

The next step is to choose the specific type. Many of MySQL's data types can store the same kind of data but vary in the range of values they can store, the precision they permit, or the physical space (on disk and in memory) they require. Some data types also have special behaviors or properties, such as DATETIME and TIMESTAMP.

More in Development Center
New on Valinv
Related Articles
Sponsored Links