Do varchar/text/longtext columns in MySQL occupy full size in filesystem even for partially filled cells?

'abcd' in CHAR(72) CHARACTER SET ascii occupies 72 bytes on disk.
'abcd' in CHAR(72) CHARACTER SET utf8 occupies 3*72 bytes on disk.
'abcd' in CHAR(72) CHARACTER SET utf8mb4 occupies 4*72 bytes on disk.
'abcd' in VARCHAR(72) occupies 1+4 bytes on disk.
'abcd' in TINYTEXT occupies 1+4 bytes on disk.
'abcd' in TEXT occupies 2+4 bytes on disk.
'abcd' in LONGTEXT occupies 4+4 bytes on disk.

Update: In some versions, InnoDB will store only 4 bytes for the CHAR cases.

Notes:
The 1,2,4 is for length; VARCHAR could be 2 in some situations.
The CHARACTER SET is important in all the cases, but does not impact the space occupied by 'abcd' except for CHAR.

@akuzminsky -- You are wrong about the *3. CHAR(N) and VARCHAR(N) can hold up to N characters in the declared CHARACTER SET. That will become up to 3*N bytes for CHAR or 1+3*N for VARCHAR.

TEXT is limited to 65535 bytes (plus 2-byte length).

There is overhead on top of the lengths described above. In InnoDB, there are record flags, transaction ids, blocking factors, pointers to overflow areas, etc, etc. So, if you use this kind of arithmetic, it will underestimate the disk space used.

MyISAM is more frugal; it has at least 1 byte of overhead per record; in some cases, only 1.

Tags:

Mysql

Varchar