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

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

Answer 2

I will post the answer in the question I made:

SELECT CHARACTER_MAXIMUM_LENGTH
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'Database'
    AND TABLE_NAME = 'Table'
    AND COLUMN_NAME = 'Field'

Answer 3

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 4

The back ticks and the char_length function in Aurelio's answer both cause errors. This answer works.