MySQL optimized database structure: data object optimized _ MySQL

Database structure optimized by MySQL: bitsCN.com optimizes the table data type using the procedure analyse function
The data type used by the table depends on the application. In MySQL, you can use the procedure analyse () function to analyze the current application table. This function can provide optimization suggestions for the data types of columns in the database, you can consider whether to implement optimization based on the actual situation of the application.
The following describes how to use the procedure analyse () function:
SELECT * FROM tbl_name procedure analyse (); SELECT * FROM tbl_name procedure analyse (16,256); each output column provides optimization suggestions for the data type of the columns in the data table. The second statement above tells procedure analyse () not to make suggestions for ENUM types that contain more than 16 or 256 bytes. Without such restrictions, the output information may be long; the ENUM definition is usually hard to read.
According to the output information of the procedure analyse () function, you may find that the fields in some tables can be changed to more efficient data types. If you decide to change the type of a field, you need to use the alter table statement.

mysql> desc statistics_news_category_history;+---------------+-------------+------+-----+---------+----------------+| Field         | Type        | Null | Key | Default | Extra          |+---------------+-------------+------+-----+---------+----------------+| id            | int(10)     | NO   | PRI | NULL    | auto_increment | | news_category | varchar(20) | YES  |     | NULL    |                | | news_num      | int(10)     | YES  |     | NULL    |                | | news_date     | varchar(10) | YES  |     | NULL    |                | +---------------+-------------+------+-----+---------+----------------+4 rows in set (0.00 sec)

mysql> select * from statistics_news_category_history procedure analyse(9,256)/G;*************************** 1. row ***************************             Field_name: datacrawldb.statistics_news_category_history.id              Min_value: 26              Max_value: 30             Min_length: 2             Max_length: 2       Empties_or_zeros: 0                  Nulls: 0Avg_value_or_avg_length: 28.0000                    Std: 1.4142      Optimal_fieldtype: TINYINT(2) UNSIGNED NOT NULL*************************** 2. row ***************************             Field_name: datacrawldb.statistics_news_category_history.news_category              Min_value: bigdata              Max_value: test             Min_length: 4             Max_length: 10       Empties_or_zeros: 0                  Nulls: 0Avg_value_or_avg_length: 6.6000                    Std: NULL      Optimal_fieldtype: ENUM('bigdata','cloud','datacenter','storage','test') NOT NULL*************************** 3. row ***************************             Field_name: datacrawldb.statistics_news_category_history.news_num              Min_value: 1              Max_value: 33             Min_length: 1             Max_length: 2       Empties_or_zeros: 0                  Nulls: 0Avg_value_or_avg_length: 19.4000                    Std: 11.9766      Optimal_fieldtype: TINYINT(2) UNSIGNED NOT NULL*************************** 4. row ***************************             Field_name: datacrawldb.statistics_news_category_history.news_date              Min_value: 2014-02-17              Max_value: 2014-02-17             Min_length: 10             Max_length: 10       Empties_or_zeros: 0                  Nulls: 0Avg_value_or_avg_length: 10.0000                    Std: NULL      Optimal_fieldtype: ENUM('2014-02-17') NOT NULL4 rows in set (0.00 sec)

Tips: 1) use the correct and appropriate types. do not store numbers as strings.
2) use the most effective (minimum) data type as much as possible. MySQL has many specialized types that save disk space and memory.
3) try to use a smaller integer to make the table smaller. For example, MEDIUMINT is often better than INT, because the MEDIUMINT column uses 25% less space.
4) if possible, the declared column is not null. It makes everything faster and saves one bit for each column. Note: If NULL is required in the application, you should use it without a doubt, but avoid having it on all columns by default.
5) for MyISAM tables, if there are no variable-length columns (VARCHAR, TEXT, or BLOB columns), use a fixed-size record format. This is fast, but unfortunately it may waste some space. Even if you have used the CREATE option to make the VARCHAR column ROW_FORMAT = fixed, you can also be prompted to use a fixed length row.
6) use the sample character set, for example, latin1. Use UTF-8 as few as possible, because UTF-8 occupies three times the space of latin1. You can use latin1 on fields that do not require UTF-8, such as mail and url.
BitsCN.com