Mysql database optimization tool PROCEDURE ANALYSE

This article will introduce you to a new command procedure analyse, which can optimize the mysql database structure. Next I will briefly introduce the usage of this function.

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: 256) the maximum number of values to be followed when analyze queries 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 max_elements value ENUM is not recommended as the data type for optimization.
Max_memory (default value: 8192) the maximum number of memories that can be allocated when analyze finds all different values in each column

The Code is as follows:	Copy code
Eg: SELECT * FROM web_member procedure analyze (1)

Sample program

1

The Code is as follows:	Copy code
Select * from students procedure analyze (); Select * from students procedure analyze (16,256 );

The second statement requires that procedure analyze () should not contain more than 16 values or enum types containing more than 256 bytes. If there is no limit, the output may be very long;

2

The Code is as follows:

Copy code

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.