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
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