How to Use “Procedure Analyse” to Optimize MySQL Table Size

MySQL provides a useful tool called “procedure analyse” that can help you optimize your tables for storage. “Procedure analyse” examines the contents of a table’s columns and suggests a field type for each column selected that reduces the total table size.

So, why bother to reduce table size when storage is cheaply available? Big tables not only use up memory, but queries on them eat up CPU usage as well. If you have a web site with large custom MySQL tables and your site is on a shared server, you could end up getting penalized monetarily or even have your site shutdown by your ISP, if queries on your MySQL tables consume more CPU than allocated. To keep this from happening you should optimize both your queries and your table size. “Procedure analyse” provides a quick and easy way to do the latter.

Because “Procedure analyse” looks at actual data within table columns, it works best when run on tables that already have a full complement of live data. Don’t run it on tables that contain test data or incomplete data as that affects it’s recommendations. Image #2

Click for full imageTo analyse a table with “procedure analyse”, you can either append “PROCEDURE ANALYSE” to an SQL query or use the admin tool, phpMyAdmin. In phpMyAdmin, look for the words, Propose Table Structure, below the list of a table’s fields and above the indices, and click on the link.

When you run “procedure analyse” with no input arguments, you will probably notice right away that there are a large number of ENUM field type recommendations…see image #2. This occurs because “procedure analyse” looks for patterns of repeated data in each field (column) it examines. This is ok for columns with a limited data set such as “yes/no” responses, but it limits a column’s flexibility because it leaves no room for future input variations without manually altering the table. To get more useful field type recommendations, you will need to add a couple input arguments to “procedure analyse” using the following syntax: SELECT * FROM my_table PROCEDURE ANALYSE(max_enumerations, max_enum_memory);Image #3

Click for full image

After some experimentation, I found that the second argument, “max_enum_memory”, is most effective in reducing the number of ENUM field-type recommendations. The default for max_enum_memory is 8,192. You’ll need to reduce it to 132 or less, to force “procedure analyse” to recommend ENUM only when column data can be grouped into small data sets. Image #3 shows an example of a procedure analyse query on the same table from the previous example, but with the parameters “(5,132)”, where “5″ is the maximum number of repeated values to assign to ENUM field type and “132″ is the maximum amount of memory to allocate to the ENUM field. The full syntax used was: select * from my_table procedure analyse(5,132);As you can see, many ENUM recommendations have now been replaced by either a number or character data type.

These “optimal” field type recommendations by “procedure analyse” represent the best possible settings for efficient storage of the table with it’s current data intact. If new data will to be added to the table, adjustments to the field types may be necessary to account for future growth before adopting these recommendations.