Use of data types for MySQL optimization

Types of choices that contribute to efficiency

1, make your data as small as possible

One of the most basic optimizations is to make your data (and index) occupy as little space on disk (and in memory) as possible. This can be a huge improvement because disk reads faster and usually uses less primary memory. Indexes also occupy a smaller number of resources if indexed on smaller columns.

You can use the following techniques to make the table more performance and minimize storage space:

• Use the most efficient (smallest) type as much as possible. MySQL has a number of specialized types that save disk space and memory.

• Use a smaller integer type if you can make the table smaller. For example, Mediumint is often better than int.

• If possible, the declaration is listed as not NULL. It makes any thing faster and you save one for each column. Note If you really need null in your application, you should use it without question, just avoid having it on all columns by default.

2, using fixed-length columns, do not use variable long 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. The tradeoff is that when you use a fixed-length column, the table takes up more space, but if you can afford it, using a fixed-length row is much faster than using a variable-length row.

3, the column is defined 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.

4. 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.

About BLOBs and text types

1. Advantages of using BLOBs and text types

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.

2. Possible drawbacks of using blobs and text types

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.

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.