Quite common task during schema review is to find the optimal data type for the column value – for example column is defined as INT but is it really needed or may be SMALLINT or even TINYINT will do instead. Does it contain any NULLs or it can be defined NOT NULL which reduces space needed and speeds up processing in most cases ?
These and similar tasks are often done by bunch of SQL queries while really MySQL has a native feature to perform this task – PROCEDURE ANALYSE
Here is sample run of PROCEDURE ANALYSE on drupal schema of Percona web site. The output contains row for each column in the table but I’ve omitted everything by few rows.
|
1 |
<br>mysql> select * from node procedure analyse() G<br>*************************** 1. row ***************************<br> Field_name: percona_drupal.node.nid<br> Min_value: 1<br> Max_value: 194<br> Min_length: 1<br> Max_length: 3<br> Empties_or_zeros: 0<br> Nulls: 0<br>Avg_value_or_avg_length: 115.8345<br> Std: 45.5192<br> Optimal_fieldtype: TINYINT(3) UNSIGNED NOT NULL<br>*************************** 2. row ***************************<br> Field_name: percona_drupal.node.vid<br> Min_value: 1<br> Max_value: 194<br> Min_length: 1<br> Max_length: 3<br> Empties_or_zeros: 0<br> Nulls: 0<br>Avg_value_or_avg_length: 115.8345<br> Std: 45.5192<br> Optimal_fieldtype: TINYINT(3) UNSIGNED NOT NULL<br>*************************** 3. row ***************************<br> Field_name: percona_drupal.node.type<br> Min_value: blog<br> Max_value: webform<br> Min_length: 4<br> Max_length: 13<br> Empties_or_zeros: 0<br> Nulls: 0<br>Avg_value_or_avg_length: 8.7482<br> Std: NULL<br> Optimal_fieldtype: ENUM('blog','contact','customer','image','lab_project','news','page','percona_faq','presentation','press_release','project','random_phrase','service','team_member','webform') NOT NULL<br> |
Here is how this table was originally defined:
|
1 |
<br>mysql> desc node;<br>+----------+------------------+------+-----+---------+----------------+<br>| Field | Type | Null | Key | Default | Extra |<br>+----------+------------------+------+-----+---------+----------------+<br>| nid | int(10) unsigned | NO | PRI | NULL | auto_increment |<br>| vid | int(10) unsigned | NO | PRI | 0 | |<br>| type | varchar(32) | NO | MUL | | |<br>| title | varchar(128) | NO | MUL | | |<br>| uid | int(11) | NO | MUL | 0 | |<br>| status | int(11) | NO | MUL | 1 | |<br>| created | int(11) | NO | MUL | 0 | |<br>| changed | int(11) | NO | MUL | 0 | |<br>| comment | int(11) | NO | | 0 | |<br>| promote | int(11) | NO | MUL | 0 | |<br>| moderate | int(11) | NO | MUL | 0 | |<br>| sticky | int(11) | NO | | 0 | |<br>+----------+------------------+------+-----+---------+----------------+<br>12 rows in set (0.00 sec)<br> |
This example shows very well the output of PROCEDURE ANALYSE or any similar tool should not be taken for an instant action but rather used as a base to make your own decision.
For example we can see nid and vid which are defined as INT in original schema can fit to TINYINT. Should we change them ? Probably not – the site will continue to growth and we expect to have more than 255 items this type would allow.
We probably could have changed it to SMALLINT or MEDIUMINT but on the small database like this it does not really yields any significant benefits so it is better to stick to default.
The type field is suggested for conversion to ENUM though as we know new types can appear by inserts we can’t do it. In other drupal installation though when types never change and savings could be significant the change may make sense.
Another important piece of information for type column is maximum length which is really helpful in more cases than ENUM data type – maximum length of data actually stored often can tell us if we defined varchar field too wide. Comparing maximum field length to average length we can see if CHAR or VARCHAR type fits better – for example if column always have same width like hashes CHAR can be more efficient storage.
In general even though I would not just use this tool suggested data types to do an ALTER TABLE it provides very helpful information for performing schema audit.
