One of the biggest annoyances in the current MySQL versions is that you still have no access to the original view definition (at least by SQL, there is a way through the file system, which I described in a former article).

So again, if you create a view like

CREATE VIEW v_checktest AS
SELECT id, val FROM checktest\n  
  WHERE val >= 1 AND val <= 5\n  
  WITH CHECK OPTION

SHOW CREATE VIEW v_checktest returns

mysql> SHOW CREATE VIEW v_checktest\G

*************************** 1. row ***************************
       View: v_checktest
Create View: CREATE ALGORITHM=UNDEFINED 
  DEFINER=`mpopp`@`localhost` 
  SQL SECURITY DEFINER VIEW `v_checktest` AS 
select `checktest`.`id` AS `id`,
  `checktest`.`val` AS `val` 
  from `checktest` 
  where ((`checktest`.`val` >= 1) 
    and (`checktest`.`val` <= 5)) 
  WITH CASCADED CHECK OPTION
1 row in set (0.00 sec)

A feature request about this issue was filed about 9 months ago, and there was a discussion at http://lists.mysql.com/internals/25571. I have added a little comment to the feature request, hoping that someone pays attention to this request again.

I have heard of other people too, who are waiting to see this being implemented. So if you do, don’t hesitate to add comments to this feature request. This might raise its priority.

I have also filed a feature request (I hope that it’s no duplicate – I have been looking for existing feature request, but didn’t find any) for SHOW TRIGGER STATUS, SHOW CREATE TRIGGER and DROP TRIGGER IF EXISTS commands.

I suppose that it’s most intuitive, if procedures, functions (where the according commands are already available), triggers, events, etc. work all with the same syntax.

You may have seen several great queries how to get useful information out of information_schema – so here’s one more:

SELECT TABLE_SCHEMA,
  sum((DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024)) as size_mb
FROM information_schema.TABLES
GROUP BY TABLE_SCHEMA
ORDER BY size_mb DESC

This query lists all of your databases with their sizes in megabytes in descending order.

If you only want to see those databases exceeding 1 MB, do the following:

SELECT TABLE_SCHEMA,
  sum((DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024)) as size_mb
FROM information_schema.TABLES
GROUP BY TABLE_SCHEMA
HAVING size_mb > 1
ORDER BY size_mb DESC

Quite simple, but very effective!

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.