It examines the result from a query and returns an analysis of the results that suggests optimal data types for each column. To obtain this analysis, append PROCEDURE ANALYSE to the end of a SELECT statement.
SELECT col1, col2 FROM table1 PROCEDURE ANALYSE();
SELECT * FROM table1 PROCEDURE ANALYSE();
Quite common task during schema review is to find the optimal data type for the column value - for example column is defined as INT but is it really needed or may be SMALLINT or even TINYINT will do instead. Read the post in mysqlperformanceblog explained in more detail.
PROCEDURE ANALYSE() can be a double edged tool. In the first it will help you to find the optimal data type for column. In the second case it had no idea you needed bigger values and gave you a recommendation based on bad data. Like most tools, PROCEDURE ANALYSE() needs to be used properly to get the desired results.
Syntax and more info here.