Why Everyone Avoids TEXT Fields 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). We are Leapcell, your top choice for hosting backend projects. Leapcell is the Next-Gen Serverless Platform for Web Hosting, Async Tasks, and Redis: Multi-Language Support Develop with Node.js, Python, Go, or Rust. Deploy unlimited projects for free pay only for usage — no requests, no charges. Unbeatable Cost Efficiency Pay-as-you-go with no idle charges. Example: $25 supports 6.94M requests at a 60ms average response time. Streamlined Developer Experience Intuitive UI for effortless setup. Fully automated CI/CD pipelines and GitOps integration. Real-time metrics and logging for actionable insights. Effortless Scalability and High Performance Auto-scaling to handle high concurrency with ease. Zero operational overhead — just focus on building. Explore more in the Documentation! Follow us on X: @LeapcellHQ Read on our blog

May 7, 2025 - 21:42

Why Everyone Avoids TEXT Fields 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).