InnoDB interesting

This morning we found something interesting about MySQL’s InnoDB tables. Apparently there is a byte limit to the rows. That means that each row can only have so many characters in it (when speaking of character based fields). Of course I don’t think we’ve ever come across this before, or it was in a MyIsam table and didn’t have that same problem, but nevertheless, the problem exists. Now the interesting part is that it’s not really the amount of data within the rows, it’s how the rows are indexed. According to documentation, there is the 8000 row byte limit, but it doesn’t exactly work just like that. For your usual varchar fields, it adds up all of those, but for text fields which naturally support over 32,000 characters, the row is storing only the first 768 bytes and then the rest of the file goes into separate pages. I’m not really sure what all of that means, but I do get that if I have 10 text fields in my row, all of which have lots of character data, it’s taking 7680 bytes just to store those, so my limit is going to be reached quickly if I have text sized data in each of those fields.

So, the solutions are to either switch the storage engine to something else like MyISAM, or to split up the tables, if the fields need to be text data type. We also can’t try to fool the database into faking the row limit by making the text fields varchar fields, that will add up even faster.

Now knowing this limit exists, I may have to go back and rethink some of the tables I’ve designed.