1

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.

Improve this question

1 Answer 1

Reset to default
1

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.

Improve this answer
2
  • I'm not really getting all the information from your links right: you mean to underline the thought my text column will be saved "withing the page" as they are not really big, so a change to varchar will not help?
    – Nanne
    Apr 9, 2013 at 12:21
  • Text fields do not get considered part of the Row Length Limits. Switching to VARCHAR/CHAR may needlessly impose such a limitation and introduce new problems (ibdata1 growth, frequent transaction checkpointing, and so forth). OK, in simple English, changing to VARCHAR will not help.
    – RolandoMySQLDBA
    Apr 9, 2013 at 12:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.