Why Use a VARCHAR Field Instead of TEXT in MySQL

Background

When storing a segment of serialized data of uncertain length in a database, many people design the field as VARCHAR(2000) in the table schema.

But if the length is uncertain, why not use the TEXT type instead? Some say: TEXT affects query performance.

Is that really the reason? This article will explore that:

What is TEXT

TEXT is a variable-length data type in MySQL, including TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. They are typically used to store large amounts of textual data, with the following storage limits:

TINYTEXT: 0 - 255 bytes
TEXT: 0 - 65,535 bytes
MEDIUMTEXT: 0 - 16,777,215 bytes
LONGTEXT: 0 - 4,294,967,295 bytes

How TEXT is Stored

Each BLOB or TEXT value is represented internally by a separately allocated object, whereas other data types have storage space allocated once per column when the table is opened.

When storing string-type data, InnoDB encodes fixed-length fields of 768 bytes or more as variable-length fields and stores them in overflow pages. Data smaller than 768 bytes is stored directly in the data row. Therefore, when using other string types, avoid storing data that is 768 bytes or larger.

Limitations of TEXT

TEXT cannot have a default value.
When indexing a TEXT field, a prefix length must be specified.
When comparing index entries, trailing spaces are padded. If a unique index is required, this can lead to duplicate key errors.
TEXT fields may be particularly long. When sorting, only the first max_sort_length bytes (default is 1024) are used. This value can be adjusted by modifying the variable:

-- View max_sort_length
SELECT @@max_sort_length;

-- Set max_sort_length
SET max_sort_length = 2048;

When processed with temporary tables, the server will use tables on disk rather than in memory, because the MEMORY storage engine does not support TEXT type.
The size of a TEXT object is determined by its type, but the actual transferable maximum size is limited by the available content and communication buffer size. This can be adjusted by changing the max_allowed_packet variable:

-- View max_allowed_packet
SELECT @@max_allowed_packet;

-- Set max_allowed_packet
SET max_allowed_packet = 67108864;

Conclusion

TEXT can be used to store large amounts of textual data. However, for several reasons, it is not recommended to use TEXT:

Performance Issues

TEXT is represented internally as a separately allocated object, requiring additional operations and resource consumption during storage and retrieval.
If a TEXT field is especially large, reading it can increase memory pressure, impacting overall system performance.
The MEMORY storage engine does not support TEXT, so when temporary tables are used, data from TEXT fields will be read from disk rather than directly from memory.

Indexing Limitations

Indexes can improve query performance, but indexing TEXT fields comes with certain restrictions and complexity:

When used as a unique index, it may result in duplicate key errors.
Creating full-text indexes requires additional computation and space to maintain. If the TEXT field is too large, it may negatively impact performance.

Therefore, it's advisable to avoid using the TEXT type in table schema design. If it must be used, consider the following approaches:

Separate TEXT fields into independent tables, linking to the main table via primary key.
Avoid reading TEXT fields unless necessary — for example, do not use SELECT *.
For large fields, consider storing them in OSS (Object Storage Service).