Optimizing Storage in the (MySQL) database of MD5

1. Storage of MD5 in MySQL database

Storing MD5 values with char (32) is a common technique. If your application uses varchar (32), it will cost an extra amount of unnecessary overhead for each string length that is worth it. This hexadecimal value can be stored in binary (16) in binary (16) data type and more efficiently using the Unhex () and Hex () functions. With this conversion, the storage space consumed by each row is reduced from 32 bytes to 16 bytes.

The following example shows the spatial comparison of the original MD5 and the compressed MD5 occupation:

This principle also applies to other 16-binary values, such as when an index is defined for the hash value of all columns.

2. Storage of IP address in MySQL

An IP address should be defined as an int unsigned data type, which takes up only 4 bytes.

Typically, a column is defined as varchar (15), which takes an average of 12 bytes. This takes an average of 12 bytes.

Such an improvement would save 2/3 of the space occupied by the column data. The Inet_aton () and Inet_ntoa () functions can manage the conversion of IP between string and numeric values.

Take a look at the following example:

　　

This technique is suitable for IPv4. With the widespread use of IPv6, it is important to store these 128bit integer values in binary (16) type rather than in the form of varchar data types that people can read.

Source: "Effective MySQL SQL statement optimization" Lishee translation.

Optimizing Storage in the (MySQL) database of MD5