The PROCEDURE ANALYSE option

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.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.