Connection between MySQL column type selection and query efficiency

What is the relationship between MySQL column type selection and query efficiency? Next we will show you how to select the MySQL column type. If you are interested in this, take a look.

To select a MySQL column type that helps make query execution faster, follow the following rules here. The "BLOB type" should be understood as including BLOB and TEXT ):

■ Use a fixed-length column instead of a variable-length column. This criterion is especially important for tables that are frequently modified and prone to fragmentation. For example, select the CHAR column instead of the VARCHAR column. The trade-off is that when a fixed-length column is used, the table occupies more space. However, if you can afford this space, using a fixed-length line is much faster than using a variable-length line.

■ Do not use long columns when short columns meet requirements. If you are using fixed-length CHAR columns, make them as short as possible. If the maximum value stored in the column is 40 characters, do not define it as CHAR (2 5 5); just define it as CHAR (40. If MEDIUMINT instead of BIGINT can be used, the table will have less disk I/O), and the value can also be processed faster.

■ Define a column as not null. This process is faster and requires less space. In addition, it can also simplify the query because it does not need to check whether there is a special case NULL.

■ Consider using the ENUM column. If a column contains only a limited number of specific values, you should consider converting it into an ENUM column. The values of the ENUM column can be processed more quickly because they are represented by numerical values internally.

■ Use procedure analyse (). If you are using MySQL3.23 or an updated version, execute procedure analyse () to view the information it provides about the columns in the table:

The corresponding output contains a column which is recommended for the best column type of each column in the table. In the second example, procedure analyse () is required not to include more than 16 values or to retrieve ENUM types larger than 256 bytes. You can change these values as needed ). Without such restrictions, the output may be very long, and the definition of ENUM may be hard to read. According to the output of procedure analyse (), you can change the table to take advantage of more effective types. If you want to change the value type, use the alter table statement.

■ Load data into BLOB. Storing packaged or unpackaged data in an application using BLOB may allow data retrieval that requires several retrieval operations to be completed in a single retrieval operation. It also helps to store data that is not easily represented by the standard table structure or data that changes over time. In Chapter 3rd, the alter table statement contains an example of a TABLE that stores results from a Web questionnaire. This example discusses how to append columns to the TABLE using alter table when adding questions to the questionnaire.

Another way to solve this problem is to let the Web application package the data into a certain data structure and insert it into a single BLOB column. This will increase the overhead of the application to decode the data and encode the records retrieved from the table), but simplifies the table structure, you do not need to change the table when changing the questionnaire. On the other hand, BLOB values also have their own inherent problems, especially when performing a large number of DELETE or UPDATE operations. Deleting BLOB will leave a large blank in the table. In the future, you will need to fill in one record or multiple records of different sizes.

■ Use optimize table for tables that are prone to fragmentation. A large number of modified tables, especially those with variable length columns, are prone to fragmentation. Fragment is not good because it creates unused space in the disk block of the storage table. As time increases, more blocks must be read to obtain valid rows, reducing performance. This problem exists in any table with variable long rows, but this problem is more prominent for blob columns because their sizes change greatly. Optimize table is often used to keep performance intact.

■ Use synthetic indexes. Merging index columns is sometimes useful. One technique is to create a hash value based on other columns and store it in an independent column. Then, you can search for the hash value to find the row. This is only valid for exact match queries. Hash Value pairs are not useful for range searches with operators such as "<" or "> = ). In MySQL 3.23 and later versions, hash values can be generated using the MD5 () function. Hash indexes are particularly useful for BLOB columns. Note that in versions earlier than MySQL3.23.2, The BLOB type cannot be indexed. Even in version 3.23.2 or later, it is faster to search for BLOB values by using hash values as the identity values.

■ Avoid retrieving large BLOB or TEXT values unless necessary. For example, the SELECT * query is not a good method unless it is certain that the WHERE clause can limit the result to the desired row. This may drag a very large BLOB value from the network without a destination. This is another scenario where BLOB identifiers stored in another column are useful. You can search for this column to determine the row you want and then retrieve the BLOB value from the specified row.

■ Isolate BLOB values in an independent table. In some cases, removing a BLOB column from a table and placing it in another sub-table may be of some significance, provided that the table can be converted to a fixed-length row format after the BLOB column is removed. This will reduce the fragmentation in the primary table and take advantage of the performance advantage of fixed-length rows.

MySQL Date and Time Functions

Transformed from the MySQL Query Class of discuz

In-depth parsing of MySQL query Cache Mechanism

Common MySQL command line tools

Non-empty question in MySQL Query