Databases Reference
In-Depth Information
disk, in memory, and in the CPU cache. They also generally require fewer CPU
cycles to process.
Make sure you don't underestimate the range of values you need to store, though,
because increasing the data type range in multiple places in your schema can be a
painful and time-consuming operation. 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 compli-
cated. Here are two examples: you should store dates and times in MySQL's built-
in types instead of as strings, and you should use integers for IP addresses. We
discuss these topics further later.
Avoid
NULL
if possible.
A lot of tables include nullable columns even when the application does not need
to store
NULL
(the absence of a value), merely because it's the default. It's usually
best to specify columns as
NOT NULL
unless you intend to store
NULL
in them.
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.
The performance improvement from changing
NULL
columns to
NOT NULL
is usually
small, so don't make it a priority to find and change them on an existing schema
unless you know they are causing problems. However, if you're planning to index
columns, avoid making them nullable if possible.
There are exceptions, of course. For example, it's worth mentioning that InnoDB
stores
NULL
with a single bit, so it can be pretty space-efficient for sparsely populated
data. This doesn't apply to MyISAM, though.
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 usu-
ally 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.
