MySQL uses the procedure analyze () function to optimize the table structure, procedureanalyse

MySQL uses the procedure analyze () function to optimize the table structure, procedureanalyse

Introduction

procedure analyse()The function is a MySQL built-in statistical analysis of MySQL Field Values to give a recommended field type.

Syntax

procesure analyse(max_elements,max_memory)

Max_elements

Specify the maximum value of a non-repeated value for each column. When this value is exceeded, MySQL does not recommend the enum type.

Max_memory

analyse()Find the maximum memory size used by all non-repeated values for each column.

Practical drills

# Analyze all columns in table t1, wing @ 3306> show create table t1; + ------- + tables ------------------------ + | Table | Create Table | + ------- + tables -------------------------- + | t1 | create table 't1' ('id' int (11) default null, 'name' varchar (16) default null, 'score 'int (11) default null) ENGINE = InnoDB default charset = utf8 | + ------- + rows -------------------------- + 1 row in set (0.00 sec) wing @ 3306> select * from t1 procedure analyze (4 ); + domains + ----------- + ------------ + domains + ------- + domains + ------------- + domains + | Field_name | Min_value | Max_value | Min_length | Max_length | bytes | Nulls | Std | optimal_fieldtype | + --------------- + ----------- + ------------ + -------------------- + ------- + ------------------------- + ------------- + wing + |. t1.id | 1 | 200000 | 1 | 6 | 0 | 0 | 100000.5000 | 116099.2790 | MEDIUMINT (6) unsigned not null | wing. t1.name | 000jxc6V | zzznmkcX | 8 | 8 | 0 | 0 | 8.0000 | NULL | CHAR (8) not null | wing. t1.score | 1 | 100 | 1 | 3 | 0 | 0 | 50.4889 | 28.8768 | TINYINT (3) unsigned not null | + --------------- + ----------- + ------------ + ------------------ + ------- + signature + ----------- + signature + 3 rows in set (0.14 sec)

Summary

The above is all about the procedure analyze () function in MySQL. I hope this article will help you in your study or work. If you have any questions, please leave a message.