MySQL Determine longest VarChar length

Question

I am trying to optimize my database. In order to do so, I need to be able to determine the longest data entry in a varchar column, and then trim the column definition to just above that.

How can I find out, using sql, the length of the longest varchar entry in my table?

CREATE TABLE `poller_output` (
  `local_data_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `rrd_name` varchar(19) NOT NULL DEFAULT '',
  `time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `output` varchar(200) NOT NULL DEFAULT '',
  PRIMARY KEY (`local_data_id`,`rrd_name`,`time`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8

Answer 1

If you want to know the max length of the field output, for example, you can use this query:

SELECT Max(CHAR_LENGTH(`output`)) AS Max FROM `poller_output`

Answer 2

You can do this by using the following statement

SELECT column_name FROM database.table PROCEDURE ANALYSE(1,1);

The Max_length value of the results from this will give you the longest varchar in that column.

So in this case assuming you want to investigate the output column then you can run the following

SELECT output FROM poller_output PROCEDURE ANALYSE(1,1);

The advantage of this is that it will also give you the optimal fieldtype in the return values which will be something along the lines of

VARCHAR(56) NOT NULL