PROCEDURE ANALYSE()

If you want to analyze the columns structures of a MySQL table and want to view the actual data in your table with certain suggestion then you can get help from PROCEDURE ANALYSE(). For example, if any data present in your table and plays a big role in any decision making then this is very useful.

Quite common task during schema review that is to find the optimal data type for the column value for e.g. column is defined as INT but is it really needed or may be SMALLINT or even TINYINT will do instead. Does it contain NULLs or it can be defined as NOT NULL which reduces the space needed and speeds up processing in most cases ?

These and similar tasks are often done by bunch of SQL queries while really MySQL has a native feature to perform this task – PROCEDURE ANALYSE.

As overall this is just a suggestion for you when table data is not big enough. For a big table you might not get the right suggestion.

In phpmyadmin you can get a link called “Propose table structure” where you will get this functionality.

Suppose you are using a INT field for your primary key and you do not have too many rows then it will suggest you to use MEDIUMINT instead or if you use a VARCHAR field and for all the rows you have limited values then you will get a suggestion to use ENUM for limited values.

Example of PROCEDURE ANALYSE():

SELECT col1, col2 FROM tbl PROCEDURE ANALYSE(10, 2000);
where 10 is max_elements and 2000 is max_memory