$dev4life = "Software Development Blog"

MySQL - Procedure Analyse

MySQL has a very useful built-in function that helps database administrators optimize the data structures and as a result, it can improve query performance and significantly decrease table size.

Procedure Analyse provides statistics on the data, such as maximum and minimum length of each data column as well as recommended optimal field type and length. In other words, if a field is set to a Signed INT (4 Bytes) and the maximum number that the field holds is no more than 255 and not less than 0, perhaps the field type should be changed to Unsigned TINYINT (1 Byte).
It also provides statistics on NULL values, empties or zeros. Using this information, database administrator can determine whether the field should be set to NOT NULL as well it may help pick the appropriate default value. Please refer to MySQL documentation for more details on data types.

This optimization is especially effective on large data sets since it could significantly decrease the size of the table and improve query performance.
Please note, in order to use PROCEDURE ANALYSE effectively, the table must be populated with some date.

SELECT * FROM transactions PROCEDURE ANALYSE(10,2000);

Procedure Analyse - database, monitoring, mysql, performance, tuning

Post a Comment