In a MySQL database (InnoDB) we have a table containing roughly 60.000.000 rows. One of the columns is of type
Currently the longest entry has length 360 (found using
SELECT MAX(LENGTH(value)) ) and the theoretical biggest value would be coming from a
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.