MySQL column type selection and MySQL query efficiency

4.3 column type selection and query efficiency
To select columns that help make your query faster, you should follow the following rules (here, "BLOB type" should be understood to include B L O B also contains the text type):
Using a fixed-length column, do not use variable-length columns. This guideline is particularly important for tables that are often modified to be easily fragmented. For example, you should select the Char column without selecting the varchar column. To weigh is the use of fixed-length columns, the table occupies more space, but if able to assume this accounted for the Lai of the  ǔば pay  Ren, every kill su silky men write  sulfone lan? br> do not use long columns when a shorter column satisfies the requirements. If you are using a fixed-length char column, you should 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), as long as it is defined as char (40). If you can use Mediumint instead of bigint, the table will be smaller (less disk I/O) and the value can be processed faster in the calculation.
Defines a column as not NULL. This process is faster and requires less space. It can also sometimes simplify the query because there is no need to check for exception null.
Consider using the enum column. If you have a column that contains only a limited number of specific values, you should consider converting it to an enum column. The values of the ENUM columns can be processed more quickly because they are internally represented numerically.
Use procedure analyse (). If you are using a MySQL3.23 or newer version, you should perform procedure analyse () to view the information it provides about the columns in the table:

A column in the corresponding output is a recommendation about the best column type for each column in the table. The second example requires procedure analyse () to not recommend an enum type that contains more than 16 values or more than 256 bytes (you can change these values as needed). Without such a restriction, the output could be long, and the definition of an ENUM would be difficult to read. Depending on the output of the procedure analyse (), you will find that you can make changes to the table to take advantage of more efficient types. If you want to change the value type, use the ALTER TABLE statement.
Load the data into B L O B. Using a BLOB to store data that is packaged or not packaged in an application may make it possible for a single retrieval operation to complete a data retrieval that originally required several retrieval operations. It is also useful for storing data that is not easily represented in a standard table structure or for data that changes over time. In the 3rd chapter of the ALTER TABLE statement, there is an example of a table that stores the results from a web questionnaire. This example discusses how to use ALTER TABLE to append columns to the table when adding problems in a questionnaire.
Another way to solve this problem is to have the application that processes the Web wrap the data into a single BLOB column, and then put it into one. This increases the cost of the application decoding the data (and encoding it after retrieving records from the table), but simplifies the structure of the table and does not make changes to the table when the questionnaire is changed. On the other hand, BLOB values have their own inherent problems, especially when doing a large number of delete or update operations. Removing a blob leaves a large white space in the table that will be populated later with a single record or possibly multiple records of different sizes.
Use optimize table for easily fragmented tables. A large number of modified tables, especially those containing variable-length columns, tend to produce fragmentation. The fragment is not good because it produces unused space in the disk block that stores the table. Over time, more blocks must be read to fetch a valid row, thereby reducing performance. This problem exists with any table with variable long rows, but this problem is more pronounced for blob columns because they vary greatly in size. Using optimize TABLE frequently helps keep performance down.
Use a composite index. It is sometimes useful to synthesize indexed columns. One technique is to create a hash value based on another column and store it in a separate column, and then find the row by searching for the hash value. This is only valid for exact matching queries. (a hash value is not useful for a range search with an operator such as "<" or "> ="). In the MySQL3.23 version and above, the hash value can be generated using the MD5 () function. Hash indexes are particularly useful for BLOB columns. One thing to note is that in previous versions of MySQL3.23.2, BLOB types cannot be indexed. Even in 3.23.2 or newer versions, using a hash value as an identity value to find a BLOB value is faster than searching the BLOB column itself.
Avoid retrieving large blobs or text values unless necessary. For example, a select * query is not a good idea unless it is certain that the WHERE clause can limit the result to the desired line. Doing so may drag very large BLOB values from the network without a destination. This is another case where the BLOB identity information stored in another column is useful. You can search the column to determine which row you want, and then retrieve the BLOB value from the qualifying row.
Isolate the BLOB value in a separate table. In some cases, it may be meaningful to move a BLOB column from a table into another table, provided that the table is converted to a fixed-length row format after the BLOB column is moved. This reduces fragmentation in the primary table and leverages the performance advantages of fixed-length rows.