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?


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.

| improve this answer | |
  • Plan B will cause a drastic increase in disk usage. We tested this same issue recently, although row width is 64k. not sure where you got the 8k from. – winmutt Aug 10 '15 at 13:14
  • 8000 excludes most of TEXT and BLOB, and possibly large VARCHARs. The "about 8000" is discussed in dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html – Rick James Aug 10 '15 at 14:40
  • The "drastic increase" probably comes from moving more text/blobs off to other storage, which is less efficiently packed. – Rick James Aug 22 '15 at 16:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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