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.
TEXT and BLOB columns are implemented differently in NDB; each row in a TEXT column is made up of two separate parts. One of these is of fixed size (256 bytes), and is actually stored in the original table. The other consists of any data in excess of 256 bytes, which is stored in a hidden table. The rows in this second table are always 2000 bytes long. This means that the size of a TEXT column is 256 if size <= 256 (where size represents the size of the row); otherwise, the size is 256 + size + (2000 × (size − 256) % 2000).
As you can see it save 256 Bytes if you have equal or less than 256 bytes.
And if you have more than 256 bytes mysql reserved 200 Bytes blocks till it can store all data
Short answer: 1MB in a MEDIUMTEXT will take at least 20 bytes in-record plus about 65 16KB blocks off-record.
TEXT and 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 ROW_FORMAT.)
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.
MEDIUMTEXT and 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 TEXT and BLOB is whether the bytes adhere to character set rules.
The only(?) differences between VARCHAR/VARBINARY and 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.