Too many TEXT fields leads to record too big

Question

I have a table with multiple 9 TEXT columns, each containing JSON data. I usually UPDATE all the columns at once. I can't seem to add a 10th TEXT column.

The table is InnoDB.

What can I do?

Answer 1

You are near or at a limit of row size. There is a limit of about 8000 bytes per record. TEXT fields, by default, store only 767 bytes toward that 8000, and store the rest elsewhere. You have enough TEXT fields to threaten the 8000.

Plan A: Have one JSON TEXT field that is a structure containing the 9 structures you now have. This will easily avoid the 8000. And, since you seem to update all of them all the time, it is probably more efficient. (If it could exceed 65KB, make it MEDIUMTEXT instead.)

Plan B: ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED would drop that 767 to 20, thereby no longer threatening to hit 8000.

Plan C: "Vertical partitioning" -- That is, put some columns in a 'parallel' table.

I prefer Plan A, but it that does not fit with your access patterns, then consider the other options.

Actually, I would go with a variant of Plan A: I would compress the JSON in the client, then store into a BLOB (TEXT won't work with compressed data). That shrinks the disk footprint, cuts back on the network traffic, etc.

Yes, a single UPDATE (setting many fields of one row) is better than multiple UPDATEs (each setting one field of the same row).

Reference: http://dev.mysql.com/doc/refman/5.6/en/innodb-row-format-dynamic.html

Another note: If all 9 text fields (as you currently have it) are all longer than 767 bytes, then they are stored in 9 different places outside the record. Plan A would lead to only 1 'overflow' place for the text.