For InnoDB (not NDB), the following applies
Short answer: 1MB in a
MEDIUMTEXT will take at least 20 bytes in-record plus about 65 16KB blocks off-record.
BLOB columns (of any size) are usually stored "off-record".
- If the contents of the column is "small", it may be stored "in-record" with the rest of the columns, like the other datatypes. (It would take a long discussion to spell out the situations; see
- When it is off-record, 0 to 767 bytes are stored on-record, together with a 20-byte pointer.
- The pointer effectively points to the block(s) containing the rest of the text or blob.
MEDIUMBLOB need a 3-byte length in addition to up to 16 megabytes (not "characters).
- 16MB is not reserved; it is variable length. The minimum is a few bytes (null bit, length, etc); the max is more than 16MB (pointer, text/blob, blocking overhead).
- The only(?) difference between
BLOB is whether the bytes adhere to character set rules.
- The only(?) differences between
TEXT/BLOB is the size limitation rules and whether an
INDEX can apply to it without "prefixing".
The other question...
And also while reading all columns of multiple rows at once i.e 100 rows, what impact will have on memory and CPU usage for reading such large size storage columns?
100MB will take I/O, memory, and some CPU.
- If the cache (buffer_pool) is cold, then a lot of I/O to fetch the data.
- If cached already, then no I/O.
- Memory: the resultset will be big, but will probably be streamed to the client.
- CPU: Not much.
- But can your client handle such a large resultset?
It is usually better to do more processing in SQL than to shovel huge amounts of data to the client.