MySQL optimization recommendations for existing tables

Author: skate
Time: 2012/05/24

 

MySQL analyzes existing tables and provides column-optimized data types to reduce the table size, for example, in French:

Select... from... where... procedure analyse ([max_elements, [max_memory])

 

Note:
Max_elements: (default value: 256) the maximum number of values that analyze follows when finding different values in each column. Analyze also uses this value to check whether the optimized data type is enum, if the number of different values in this column exceeds the value of max_elements, Enum is not recommended as the data type for optimization.

Max_memory: (default value: 8192) Maximum number of memories that can be allocated when analyze finds all different values in each column

Mysql> select parent_id from child procedure analyse (5, 2000) \ G
* *************************** 1. row ***************************
Field_name: Test. Child. parent_id
Min_value: NULL
Max_value: NULL
Min_length: 0
Max_length: 0
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 0.0
STD: 0.0
Optimal_fieldtype: Char (0) not null // This is an optimization suggestion
1 row in SET (0.00 Sec)

 

After a certain period of time in the database production environment, the DBA can refer to the analysis results of analyze to optimize the table structure. To reduce tables
Size

 

---------- END ----------