Schema optimization and indexing

The smaller the better.

In general, try to choose a small data type that is sufficient to fit your storage and presentation data. The smaller data types are also often faster because they use less hard disk space, memory, and CPU caching. They also require less CPU processing cycles.

Be sure not to underestimate the range of values you want to store, because in multiple locations of your schema, the range of data types that you grow can make it difficult and time-consuming to operate. If you do not know what type of data to choose, then choose the smallest, of course, to ensure that the data does not cross.

The simple is good.

For simple data type operations, there is no need for too many CPU cycles. For example, the comparison between the integral types is lower than the comparison between the characters. Because character sets and collations make the characters more complex. There are two examples of storing dates and times to use MySQL built-in types instead of strings, and for IP addresses you should use an integral type. We will discuss them in detail in a later section.

If possible, avoid using null

When possible, you should define not NULL. There are many tables that contain many null columns, even if they do not store null. Just because it's the default. Unless you intend to store null, otherwise you should be careful to specify column NOT NULL.

For MySQL, it is very difficult to optimize some statements involving null columns, because they are indexed, indexed, and values are more complex. A null column requires more storage space and requires special handling within MySQL. When a null column is indexed, each of its entities requires additional bytes, and the setting causes the fixed-length index to be converted to a variable size (variable-sized) index in MyISAM.

Even if you need to not store values in the field, you should not use NULL. Consider using 0, a special number, or an empty string to replace NULL.

Increasing the performance of NULL columns to NOT NULL columns is very small. So don't change existing schemas unless you know the problems that can arise from doing so. However, if you plan to index columns, avoid using NULL columns if possible.

The first step is to determine the applicable type of the specified column, and probably know what kind of value, string, time, and so on. This is straightforward, but we mention that in some special cases, choosing a data type is not straightforward.

The second step is to select a specific type. Many MySQL data types hold the same type of data, but they store different ranges, allowable precision, or the physical space (or hard disk and memory) they need. Some data types have special behavior or properties.

For example, datetime and timestamp columns can store the same type of data: Date and time, with a precision of seconds. However, timestamp only uses half of the storage space and has an automatic update feature. On the other hand, it has a small range of values, and sometimes this feature becomes an obstacle.

We are talking about basic types. MySQL is also compatible with aliases, such as Integer,bool and numeric. These are just aliases. They may cause some confusion, but they will not affect performance.