Andrew recently wrote about his plans to take the MySQL Certification exams and that he finds interesting new things that he learns from his studies. I can really confirm this – I also learnt a lot when I studied for my exams, which I took (and passed) in autumn last year.
This is a great opportunity to tell you about the PROCEDURE ANALYSE option, which I learn about in the Study Guide. I believe that this option isn’t well known, since I didn’t hear about it anywhere else than in the Study Guide – so it might be new to some people.
The best way to show you what it does is by an example – lets execute it on the sakila.film table (using the Sakila sample database, version 0.6):
mysql> SELECT * FROM film PROCEDURE ANALYSE(10, 30)\G
*************************** 1. row ***************************
Field_name: sakila.film.film_id
Min_value: 1
Max_value: 1000
Min_length: 1
Max_length: 4
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 500.5000
Std: 288.6750
Optimal_fieldtype: SMALLINT(4) UNSIGNED NOT NULL
*************************** 2. row ***************************
Field_name: sakila.film.title
Min_value: ACADEMY DINOSAUR
Max_value: ZORRO ARK
Min_length: 8
Max_length: 27
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 14.2350
Std: NULL
Optimal_fieldtype: VARCHAR(27) NOT NULL
*************************** 3. row ***************************
Field_name: sakila.film.description
Min_value: A Action-Packed Character Study
of a Astronaut And a Explorer who must Reach a
Monkey in A MySQL Convention
Max_value: A Unbelieveable Yarn of a
Student And a Database Administrator who must Outgun
a Husband in An Abandoned Mine Shaft
Min_length: 70
Max_length: 130
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 93.8420
Std: NULL
Optimal_fieldtype: VARCHAR(130) NOT NULL
*************************** 4. row ***************************
Field_name: sakila.film.release_year
Min_value: 2006
Max_value: 2006
Min_length: 4
Max_length: 4
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 2006.0000
Std: 0.0000
Optimal_fieldtype: ENUM('2006') NOT NULL
*************************** 5. row ***************************
Field_name: sakila.film.language_id
Min_value: 1
Max_value: 1
Min_length: 1
Max_length: 1
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 1.0000
Std: 0.0000
Optimal_fieldtype: ENUM('1') NOT NULL
*************************** 6. row ***************************
Field_name: sakila.film.original_language_id
Min_value: NULL
Max_value: NULL
Min_length: 0
Max_length: 0
Empties_or_zeros: 0
Nulls: 1000
Avg_value_or_avg_length: 0.0
Std: 0.0
Optimal_fieldtype: CHAR(0)
*************************** 7. row ***************************
Field_name: sakila.film.rental_duration
Min_value: 3
Max_value: 7
Min_length: 1
Max_length: 1
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 4.9850
Std: 1.4109
Optimal_fieldtype: TINYINT(1) UNSIGNED NOT NULL
*************************** 8. row ***************************
Field_name: sakila.film.rental_rate
Min_value: 0.99
Max_value: 4.99
Min_length: 6
Max_length: 14
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 2.980000
Std: 1.645570
Optimal_fieldtype: DECIMAL(13, 2) NOT NULL
*************************** 9. row ***************************
Field_name: sakila.film.length
Min_value: 46
Max_value: 185
Min_length: 2
Max_length: 3
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 115.2720
Std: 40.4061
Optimal_fieldtype: TINYINT(3) UNSIGNED NOT NULL
*************************** 10. row ***************************
Field_name: sakila.film.replacement_cost
Min_value: 9.99
Max_value: 29.99
Min_length: 14
Max_length: 14
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 19.984000
Std: 6.047807
Optimal_fieldtype: DECIMAL(13, 2) NOT NULL
*************************** 11. row ***************************
Field_name: sakila.film.rating
Min_value: G
Max_value: R
Min_length: 1
Max_length: 5
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 2.9260
Std: NULL
Optimal_fieldtype: VARCHAR(5) NOT NULL
*************************** 12. row ***************************
Field_name: sakila.film.special_features
Min_value: Behind the Scenes
Max_value: Trailers,Deleted Scenes,Behind the Scenes
Min_length: 8
Max_length: 54
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 28.0510
Std: NULL
Optimal_fieldtype: SET NOT NULL
*************************** 13. row ***************************
Field_name: sakila.film.last_update
Min_value: 2006-02-14 22:03:42
Max_value: 2006-02-14 22:03:42
Min_length: 19
Max_length: 19
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 19.0000
Std: NULL
Optimal_fieldtype: ENUM('2006-02-14 22:03:42') NOT NULL
13 rows in set (0.01 sec)
You see, you get an interesting output about table statistics which can help you to optimize the table. Use the parameters after PROCEDURE ANALYSE to specify 1) the maximum number of elements and 2) the maximum number of characters to be used in the Optimal_fieldtype field. This can avoid the output of long ENUM lists (which might not be useful in most cases).
Lets change the values and see the effect:
mysql> SELECT * FROM film PROCEDURE ANALYSE(3, 10)\G
*************************** 1. row ***************************
Field_name: sakila.film.film_id
Min_value: 1
Max_value: 1000
Min_length: 1
Max_length: 4
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 500.5000
Std: 288.6750
Optimal_fieldtype: SMALLINT(4) UNSIGNED NOT NULL
*************************** 2. row ***************************
Field_name: sakila.film.title
Min_value: ACADEMY DINOSAUR
Max_value: ZORRO ARK
Min_length: 8
Max_length: 27
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 14.2350
Std: NULL
Optimal_fieldtype: VARCHAR(27) NOT NULL
*************************** 3. row ***************************
Field_name: sakila.film.description
Min_value: A Action-Packed Character Study
of a Astronaut And a Explorer who must Reach a
Monkey in A MySQL Convention
Max_value: A Unbelieveable Yarn of a
Student And a Database Administrator who must Outgun
a Husband in An Abandoned Mine Shaft
Min_length: 70
Max_length: 130
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 93.8420
Std: NULL
Optimal_fieldtype: VARCHAR(130) NOT NULL
*************************** 4. row ***************************
Field_name: sakila.film.release_year
Min_value: 2006
Max_value: 2006
Min_length: 4
Max_length: 4
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 2006.0000
Std: 0.0000
Optimal_fieldtype: YEAR NOT NULL
*************************** 5. row ***************************
Field_name: sakila.film.language_id
Min_value: 1
Max_value: 1
Min_length: 1
Max_length: 1
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 1.0000
Std: 0.0000
Optimal_fieldtype: TINYINT(1) UNSIGNED NOT NULL
*************************** 6. row ***************************
Field_name: sakila.film.original_language_id
Min_value: NULL
Max_value: NULL
Min_length: 0
Max_length: 0
Empties_or_zeros: 0
Nulls: 1000
Avg_value_or_avg_length: 0.0
Std: 0.0
Optimal_fieldtype: CHAR(0)
*************************** 7. row ***************************
Field_name: sakila.film.rental_duration
Min_value: 3
Max_value: 7
Min_length: 1
Max_length: 1
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 4.9850
Std: 1.4109
Optimal_fieldtype: TINYINT(1) UNSIGNED NOT NULL
*************************** 8. row ***************************
Field_name: sakila.film.rental_rate
Min_value: 0.99
Max_value: 4.99
Min_length: 6
Max_length: 14
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 2.980000
Std: 1.645570
Optimal_fieldtype: DECIMAL(13, 2) NOT NULL
*************************** 9. row ***************************
Field_name: sakila.film.length
Min_value: 46
Max_value: 185
Min_length: 2
Max_length: 3
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 115.2720
Std: 40.4061
Optimal_fieldtype: TINYINT(3) UNSIGNED NOT NULL
*************************** 10. row ***************************
Field_name: sakila.film.replacement_cost
Min_value: 9.99
Max_value: 29.99
Min_length: 14
Max_length: 14
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 19.984000
Std: 6.047807
Optimal_fieldtype: DECIMAL(13, 2) NOT NULL
*************************** 11. row ***************************
Field_name: sakila.film.rating
Min_value: G
Max_value: R
Min_length: 1
Max_length: 5
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 2.9260
Std: NULL
Optimal_fieldtype: VARCHAR(5) NOT NULL
*************************** 12. row ***************************
Field_name: sakila.film.special_features
Min_value: Behind the Scenes
Max_value: Trailers,Deleted Scenes,Behind the Scenes
Min_length: 8
Max_length: 54
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 28.0510
Std: NULL
Optimal_fieldtype: SET NOT NULL
*************************** 13. row ***************************
Field_name: sakila.film.last_update
Min_value: 2006-02-14 22:03:42
Max_value: 2006-02-14 22:03:42
Min_length: 19
Max_length: 19
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 19.0000
Std: NULL
Optimal_fieldtype: TIMESTAMP NOT NULL
13 rows in set (0.01 sec)
You can see, there are no longer any ENUM fields.