Procedure analyse provides optimization suggestions for each column of an existing table by analyzing the SELECT query results.
Procedure analyse Syntax:
Select... from... where... procedure analyse ([ Max_elements
,[ Max_memory
])
Max_elements
(Default value: 256) the maximum number of values to be followed when analyze finds 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
Max_elements Value
Enum is not recommended as the data type optimized
.
Max_memory
(Default value: 8192) the maximum number of memories that can be allocated when analyze finds all different values in each column.
Example Program
Bytes ------------------------------------------------------------------------------------
Mysql> DESC user_account;
+ ----------- + ------------------ + ------ + ----- + --------- + ---------------- +
| FIELD | type | null | key | default | extra |
+ ----------- + ------------------ + ------ + ----- + --------- + ---------------- +
| Userid | int (10) unsigned | no | pri | null | auto_increment |
| Username | varchar (10) | no | null |
| Passsword | varchar (30) | no | null |
| Groupname | varchar (10) | Yes | null |
+ ----------- + ------------------ + ------ + ----- + --------- + ---------------- +
4 rows in SET (0.00 Sec)
Mysql> select * From user_account procedure analyse (1) \ G;
* *************************** 1. row ***************************
Field_name: ibatis. user_account.userid
Min_value: 1
Max_value: 103
Min_length: 1
Max_length: 3
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 51.7500
STD: 50.2562
Optimal_fieldtype: tinyint (3) unsigned not null
* *************************** 2. row ***************************
Field_name: ibatis. user_account.username
Min_value: DFSA
Max_value: lmeadors
........................................ .................
Bytes ---------------------------------------------------------------------------------------
From the output in the first row, we can see that analyze analyzes the minimum value of the ibatis. user_account.userid column 1, the maximum value is 103, and the minimum length is 1,
Maximum length 3..., and provides optimization suggestions for modifying the field: We recommend that you change the data type of this field to tinyint (3) unsigned not null.
Http://www.baobaoke.com/space.php? Uid = 179 & Do = Blog & id = 33484