MySQL supports a large variety of data types, and choosing the correct type to store your data is crucial to getting good performance. The following simple guidelines can help you make better choices, no matter what type of data you are storing:
Smaller is usually better.
In general, try to use the smallest data type that can correctly store and represent
your 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.
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 complicated. 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.
Avoid NULL if possible.
You should define fields as NOT NULL whenever you can. 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. You should be careful 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.
Even when you do need to store a “no value” fact in a table, you might not need to use NULL. Consider using zero, a special value, or an empty string instead.
The performance improvement from changing NULL columns to NOT NULL is usually small, so don’t make finding and changing them on an existing schema a priority unless you know they are causing problems. However, if you’re planning to index columns, avoid making them nullable if possible.
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.
For example, a DATETIME and a TIMESTAMP column can store the same kind of data: date and time, to a precision of one second. However, TIMESTAMP uses only half as much storage space, is time zone–aware, and has special autoupdating capabilities. On the other hand, it has a much smaller range of allowable values, and sometimes
its special capabilities can be a handicap.
MySQL supports many aliases for compatibility, such as INTEGER, BOOL, and NUMERIC. These are only aliases. They can be confusing, but they don’t affect performance.
Source of Information : OReIlly High.Performance MySQL Second.Edition
Smaller is usually better.
In general, try to use the smallest data type that can correctly store and represent
your 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.
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 complicated. 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.
Avoid NULL if possible.
You should define fields as NOT NULL whenever you can. 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. You should be careful 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.
Even when you do need to store a “no value” fact in a table, you might not need to use NULL. Consider using zero, a special value, or an empty string instead.
The performance improvement from changing NULL columns to NOT NULL is usually small, so don’t make finding and changing them on an existing schema a priority unless you know they are causing problems. However, if you’re planning to index columns, avoid making them nullable if possible.
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.
For example, a DATETIME and a TIMESTAMP column can store the same kind of data: date and time, to a precision of one second. However, TIMESTAMP uses only half as much storage space, is time zone–aware, and has special autoupdating capabilities. On the other hand, it has a much smaller range of allowable values, and sometimes
its special capabilities can be a handicap.
MySQL supports many aliases for compatibility, such as INTEGER, BOOL, and NUMERIC. These are only aliases. They can be confusing, but they don’t affect performance.
Source of Information : OReIlly High.Performance MySQL Second.Edition
|
|
0 comments
Post a Comment