MySQL: How to find an optimal datatype for the columns

PROCEDURE ANALYSE examines the result from a query and returns an analysis of the results that suggests optimal data types for each column that may help reduce table sizes. Simply append PROCEDURE ANALYSE to the end of a select statement:

SELECT f1, f2 FROM t PROCEDURE ANALYSE(20, 2000);

where

  • 10 is is the maximum number of distinct values which is checked for columns;
  • 2000 is the maximum amount of memory which can be allocated per column while trying to find all distinct values.

This MySQL feature can be helpful after importing new data or for checking your existing tables to verify whether columns datatype was designed providently.

No Comment

No comments yet

Leave a reply