38.1. General Table Optimizations

This section discusses some general table-design principles that apply for any storage engine.

• Use proper indexing for your tables so that MySQL can look up rows faster by value rather than by performing table scans.

• Use columns that are no longer than necessary. For example, don't use BIGINT if MEDIUMINT will do, or CHAR(255) if strings are never more than 100 characters long. Shorter columns require less storage and can be compared faster. If a column is indexed, making the column smaller allows more key values to fit in the index cache, which improves performance of index-based queries.

• Pay special attention to columns that are used to join one table to another. They should be indexed for fast lookup, and shorter columns can be compared to each other more quickly. Use the same data type for joined columns because, in general, like types can be compared faster than unlike types. (CHAR and VARCHAR columns that are declared as the same length are the same for comparison purposes.)

• Define columns as NOT NULL if possible. Allowing NULL values in a column complicates column processing somewhat because the query processor has to treat NULL values specially in some contexts. This results in a slight speed penalty.

• To analyze column contents, use PROCEDURE ANALYSE().

• Normalize your tables.

• Use summary tables.

Normalization is discussed later in this chapter. For further information on indexing techniques and summary tables, see Chapter 22, "Basic Optimizations."

Use of PROCEDURE ANALYSE() can help you determine whether columns can be redefined to smaller data types. It can also determine whether a column contains only a small number of values and could be defined as an ENUM. To tell PROCEDURE ANALYSE() not to suggest long ENUM definitions, pass it two arguments indicating the maximum number of elements and number of characters allowed in the definition. The following example shows the column types that PROCEDURE ANALYSE() suggests for the CountryLanguage table:

 mysql> SELECT * FROM CountryLanguage PROCEDURE ANALYSE(10,256)\G *************************** 1. row ***************************              Field_name: world.CountryLanguage.CountryCode               Min_value: ABW               Max_value: ZWE              Min_length: 3              Max_length: 3        Empties_or_zeros: 0                   Nulls: 0 Avg_value_or_avg_length: 3.0000                     Std: NULL       Optimal_fieldtype: CHAR(3) NOT NULL *************************** 2. row ***************************              Field_name: world.CountryLanguage.Language               Min_value: Abhyasi               Max_value: [South]Mande              Min_length: 2              Max_length: 25        Empties_or_zeros: 0                   Nulls: 0 Avg_value_or_avg_length: 7.1606                     Std: NULL       Optimal_fieldtype: VARCHAR(25) NOT NULL *************************** 3. row ***************************              Field_name: world.CountryLanguage.IsOfficial               Min_value: F               Max_value: T              Min_length: 1              Max_length: 1        Empties_or_zeros: 0                   Nulls: 0 Avg_value_or_avg_length: 1.0000                     Std: NULL       Optimal_fieldtype: ENUM('F','T') NOT NULL *************************** 4. row ***************************              Field_name: world.CountryLanguage.Percentage               Min_value: 0.1               Max_value: 100.0              Min_length: 3              Max_length: 5        Empties_or_zeros: 65                   Nulls: 0 Avg_value_or_avg_length: 20.4                     Std: 30.8       Optimal_fieldtype: FLOAT(3,1) NOT NULL 

The actual types used in the table are shown by DESCRIBE:

 mysql> DESCRIBE CountryLanguage; +-------------+---------------+------+-----+---------+-------+ | Field       | Type          | Null | Key | Default | Extra | +-------------+---------------+------+-----+---------+-------+ | CountryCode | char(3)       | NO   | PRI |         |       | | Language    | char(30)      | NO   | PRI |         |       | | IsOfficial  | enum('T','F') | NO   |     | F       |       | | Percentage  | float(4,1)    | NO   |     | 0.0     |       | +-------------+---------------+------+-----+---------+-------+ 

Comparing the two results, it appears that CountryLanguage already is defined fairly optimally.