When you’re building a database for a web application, one of the most impactful decisions is the data type you choose for text data fields. MySQL provides multiple string data types, each with unique characteristics and trade-offs. The data type you choose affects how much data you can store, where it’s stored, the functionality available to you, and the performance of everyday database operations.
In this article, we’ll look at one frequently asked question concerning MySQL string data types: should you use a VARCHAR column or a TEXT column to store textual website data?
MySQL VARCHAR vs. TEXT: The Short Answer
If you’re looking for a TL;DR, it’s this: use VARCHAR if your data is of variable length and you know it fits into VARCHAR’s 65,535 character limit. In most circumstances, VARCHAR provides better performance, it’s more flexible, and can be fully indexed. If you need to store longer strings, use MEDIUMTEXT or LONGTEXT, but be aware that very large amounts of data can be stored in columns of these types.
MySQL VARCHAR vs. TEXT: The Longer Answer
To explain why VARCHAR is the right default choice, we’ll need to look at MySQL’s string data formats. For our purposes, these fall into two main groups, CHAR and VARCHAR types and the TEXT types, which are based on MySQL’s binary object BLOB types. There are other string types—SET and ENUM—but they’re not relevant to this article.
We’ll be talking about characters and the amount of space they consume. However, we’re not going to discuss the precise definition of a character or the effect of using different character sets. Take a look at the MySQL documentation to learn more about character lengths and multibyte character sets.
First, CHAR and VARCHAR:
- CHAR is a fixed-length column with a maximum length of 255 characters. You declare the size when you create the table. If you store fewer characters than the fixed length, MySQL pads the remainder with spaces. Therefore, CHAR always consumes the same amount of storage.
- VARCHAR columns store variable-length strings up to 65,535 characters. You specify the maximum length that you intend to store when you create the table. VARCHAR columns are not padded, which means they consume as much space as the length of the string (plus one or two bytes for a prefix that indicates the string’s length).
CHARs are great for storing short strings when you know how long they are. You can also use VARCHAR to store short strings—VARCHAR(40), for example—but it can store any string up to the maximum column size, using a variable amount of storage space.
Next, TEXT and its variants. Text is based on the BLOB (binary large object) type. These are variable-length data types, and they come in three main variants:
- TEXT has a maximum length of 65,535 bytes—the same as VARCHAR.
- MEDIUMTEXT has a maximum length of about 16 megabytes.
- LONGTEXT has a maximum length of about 4 gigabytes.
In many respects, TEXT is similar to VARCHAR: it stores variable-length strings up to 65,535 bytes. For both, the amount of space consumed depends on the length of the string. However, there are some key differences:
- TEXT columns can only be indexed for a specified length. MySQL doesn’t index the full length of TEXT data types (except for full-text search indexes).
- The full length of the string can’t be used for sorting.
- Queries involving TEXT columns can lead to the creation of a temporary table on disk instead of in memory. This is because MySQL’s memory storage engine does not support TEXT data types. There is a significant performance penalty to using disk-based tables, which means queries using TEXT can be much slower.
- TEXT data may be stored off the table, with a pointer to the string stored on the table. Accessing data stored in this way is slower. VARCHAR data is always stored on the table. If data is frequently retrieved, inline storage offers faster performance.
Taken together, these qualities of TEXT mean that, for many circumstances involving a web application, it’s preferable to use VARTEXT when storing variable-length text of fewer than 65,535 characters.
None of this means that you must use VARCHAR. TEXT is a reasonable choice if the limitations we’ve described are unimportant to you. However, there are circumstances where TEXT has undesirable consequences that don’t arise if you use VARCHAR.
If you want to store longer variable-length strings, MEDIUMTEXT and LONGTEXT are your best option. However, you may run into network and other system limitations if you try to insert large amounts of data. Throwing multi-gigabyte BLOBs into your database is rarely wise, so be sure to check the data’s size before storing it.
VARCHAR in Action
To see a real-life example of how these MySQL data types are used, let’s explore a couple of tables in WordPress’s database with cPanel’s built-in phpMyAdmin tools. You can examine the data types of any MySQL database on your server by opening phpMySQL, locating the database in the left-hand column, and clicking the Structure link adjacent to the relevant table.
If you look at the comments table, you’ll see that WordPress’s developers have used VARCHAR for shorter text fields: comment type, comment agent, comment author, URL, and so on. But they have chosen to use a TEXT field for the comment content, a column unlikely to be used in an index.
In the post table, a similar pattern is evident. VARCHAR for post status, post password, and post name. The post content column, which is likely to exceed VARCHAR’s space limit, is stored in a LONGTEXT column with a theoretical maximum of 4 GB.
PhpMySQL is just one of the many web hosting, database management, and automation tools built into cPanel & WHM. You can read more about managing database with cPanel in:
As always, if you have any feedback or comments, please let us know. We are here to help in the best ways we can. You’ll find us on Discord, the cPanel forums, and Reddit. Be sure to also follow us on Facebook, Instagram, and Twitter.
