Bug #14102 PROCEDURE ANALYSE() should have Current_fieldtype in addition to Optimal_fieldt
Submitted: 18 Oct 2005 1:02 Modified: 3 Apr 2009 5:38
Reporter: Tobias Asplund Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:5.0.13-rc, all OS:Any (MS Windows/FreeBSD/Linux)
Assigned to: CPU Architecture:Any
Triage: D5 (Feature request)

[18 Oct 2005 1:02] Tobias Asplund
Description:
Using PROCEURE ANALYSE you will in 99.9% of the cases use it to make sure you're currently using the optimal column types for your tables.
On a table with, say 30 columns, this becomes tedious since the current column type isn't listed anywhere.

How to repeat:
SELECT * FROM tmp PROCEDURE ANALYSE(0,0)\G
*************************** 1. row ***************************
             Field_name: tmp.tmp.a
              Min_value: 1
              Max_value: 2
             Min_length: 1
             Max_length: 1
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 1.5000
                    Std: 0.5000
      Optimal_fieldtype: TINYINT(1) UNSIGNED NOT NULL
*************************** 2. row ***************************
             Field_name: tmp.tmp.b
              Min_value: a
              Max_value: b
             Min_length: 1
             Max_length: 1
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 1.0000
                    Std: NULL
      Optimal_fieldtype: CHAR(1) NOT NULL

Suggested fix:
What I propose would be this instead: (to make it easier to browse for bad column types)

SELECT * FROM tmp PROCEDURE ANALYSE(0,0)\G
*************************** 1. row ***************************
             Field_name: tmp.tmp.a
              Min_value: 1
              Max_value: 2
             Min_length: 1
             Max_length: 1
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 1.5000
                    Std: 0.5000
      Current_fieldtype: INTEGER
      Optimal_fieldtype: TINYINT(1) UNSIGNED NOT NULL
*************************** 2. row ***************************
             Field_name: tmp.tmp.b
              Min_value: a
              Max_value: b
             Min_length: 1
             Max_length: 1
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 1.0000
                    Std: NULL
      Current_fieldtype: CHAR(50)
      Optimal_fieldtype: CHAR(1) NOT NULL
[3 Apr 2009 5:38] Sveta Smirnova
Thank you for the reasonable feature request.