Changing TEXT to VARCHAR In a MySQL database (InnoDB) we have a table containing roughly 60.000.000 rows. One of the columns is of type text.

Currently the longest entry has length 360  (found using SELECT MAX(LENGTH(value)) ) and the theoretical biggest value would be coming from a varchar(511) column.
There is no index on the column.

I was advised to try and see if we can move this column from text to something else, but I'm struggling to find out if this will help at all. I have done some research of course, but I would like too see if I'm correct. I know 'random advice' is not too be taken too seriously, but at first glance it seemed to me it has some merit: text is big, and max of 511 chars isn't that big.

But, as far as I can find, (bigger?) varchar/varbinary and text/blob are handled the same when using InnoDB. The issue one might face with large values is that not everything is stored in the same 'row' (page? not sure of the terminology here) on the disc when using text. There are several sources that say this is a misunderstanding as some of the text is actually saved in the same place a normal row would be saved. (I hope I'm describing this right)

In the end, my conclusion was that there isn't any specific extra value in changing this column to some sort of varchar (probably varchar(511)) at the moment.
This sounds vaguely familiar. Oh yea, I wrote about this back on Aug 14, 2012  : error 1118: row size too large. utf8 innodb. Here is additional information from a Percona Expert on this subject : InnoDB create table error: “Row size too large”

I can see this putting a strain on an InnoDB table in terms of row length limits. Turning TEXT fields of ominous lengths into VARCHARs or even CHARs in InnoDB will have no effect (at least, no positive effects).

Your concern is quite valid and merits some respect. Why?

Some would do one of the following:


Perform the conversion and deal with (mostly complain about) worse consequences
Perform the conversion in a Development server, see the effects, and judge accordingly
Ask why, do research, carry out due diligence in a Development server


It sounds like you did Option #3 from the above list. Your conclusion, given the lengths of data, is correct for your given dataset.