Row size limits in Mysql w.r.t to TEXT and BLOB columns

Question

There is a well-known limitation in Mysql for row size which 65,535 bytes. While going through the docs,

I have found that

BLOB and TEXT columns only contribute 9 to 12 bytes toward the row size limit because their contents are stored separately from the rest of the row.

I did go through the MySQL documentation but unable to understand when a TEXT column will contribute 9 bytes and when it will contribute 12 bytes. Can anyone explain how to interpret the statement?

• Akina: TINYTEXT/TINYBLOB needs 1 byte for data length store, LONGTEXT/LONGBLOB - 4 bytes. Plus 8 bytes ( I think - for reference store and NULL mark, and maybe something else - reference needs 6 bytes, NULL mark 1 byte ).
• mustaccio: @Akina you shouldn't answer in comments; please consider posting what you said as a proper answer.
• Akina: @mustaccio I am not sure in the most part of a comment - only in where the difference is taken from, but it is not enough for the answer. If somebody may create full answer - he may use the info from my comment freely. Maybe, you?

Answer 1

It is also stored the length of the data, that is why it is 9 - 12 , if you want it exactly.

https://github.com/jeremycole/mysql/blob/master/storage/innobase/include/btr0cur.h#L762

/** The reference in a field for which data is stored on a different page.
The reference is at the end of the 'locally' stored part of the field.
'Locally' means storage in the index record.
We store locally a long enough prefix of each column so that we can determine
the ordering parts of each index record without looking into the externally
stored part. */
/*-------------------------------------- @{ */
#define BTR_EXTERN_SPACE_ID 0 /*!< space id where stored */
#define BTR_EXTERN_PAGE_NO 4 /*!< page no where stored */
#define BTR_EXTERN_OFFSET 8 /*!< offset of BLOB header
 on that page */
#define BTR_EXTERN_LEN 12 /*!< 8 bytes containing the
 length of the externally
 stored part of the BLOB.
 The 2 highest bits are
 reserved to the flags below. */
/*-------------------------------------- @} */

• Rick James: That sounds like 20 bytes, which I have heard elsewhere.
• nbk: the last 8 bytes are moved.to the storage space as header /* #define BTR_EXTERN_FIELD_REF_SIZE 20 // moved to btr0types.h */

Answer 2

There additional complexities in row format.

What has been discussed is InnoDB; MyISAM works differently.

InnoDB has 4 ROW_FORMATs. You should probably look at those if you would like some flexibility.

The general rule for InnoDB is to focus on where and how rows are stored. In this, the limit is about 8KB; more specifically a little less than half the block size (default = 16KB). This limit overrides the 64KB that is referring to something internal; it is not relevant to users.

TEXT and BLOB columns are stored "on-record" or "off-record" (aka "overflow") based on the ROW_FORMAT and the data:

• A TEXT/BLOB that is <= 40 bytes, it is stored on-record.

• Medium-sized columns are stored either on- or off-record depending on their total size.

• There is a 2-byte length field in front of actual strings.

• Big value: COMPACT will store the first 768 bytes of long columns are stored on-record. DYNAMIC uses 20 bytes to 'point' to off-record.

• COMPRESSED = DYNAMIC + compression. Caution: The compressed block tends to be half the size of the 'real' block.

• (This discussion is incomplete.)

If you don't need all the big columns, don't use SELECT *; instead, specify the needed columns. This avoids the off-record disk hit(s).

(I have not heard of the 9/12 thing you mentioned.)