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
The table is InnoDB.
What can I do?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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
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
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).
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.