Does large text datatypes reserves storage on disk?

Question

I'm design an DB for an app, in a table there is a column MEDIUMTEXT which according to mysql doc can storage on disk upto 16MB.

So my question is if I'm storing a text of 1Mb in this column, will it still reserves 16MB storage/block on disk for this particular record.

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?

Answer 1

Mot eactly, see mysql manual

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

Answer 2

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.

Long answer:

• 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.