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.