Monday, December 26, 2016

analyazing mysql column length using procedure analyse

Sponsored Links
We all do mysql optimization. It is important to adjust the column length for each field so that memory will not be wasted.

Suppose there is a table named people and there is a column email_id in it. We would like to know what is the current minimum and maximum values of this field. So that if its defined to high, we can bring it down. How we can check this?

We can use mysql procedure analyse to check this.

For eg:

mysql> select email_id from people procedure analyse();
+------------------------+------------+--------------------------+------------+------------+------------------+-------+-------------------------+------+----------------------+
| Field_name             | Min_value  | Max_value                | Min_length | Max_length | Empties_or_zeros | Nulls | Avg_value_or_avg_length | Std  | Optimal_fieldtype    |
+------------------------+------------+--------------------------+------------+------------+------------------+-------+-------------------------+------+----------------------+
| people.people.email_id | 1234234234 | abcdefline_94@hotmail.de |          4 |         39 |              287 |     0 | 18.6437                 | NULL | VARCHAR(39) NOT NULL |
+------------------------+------------+--------------------------+------------+------------+------------------+-------+-------------------------+------+----------------------+
1 row in set (0.00 sec)
mysql>


In the above example, we can see that minimum value of the email id is of length 4 and maximum value is 39. So if we had define varchar(100) for email_id, we can bring it down to varchar(60) or near to that.
Sponsored Links

No comments:

Post a Comment

Be nice. That's all.