While creating a database for web applications, it is crucial to pick the correct data type for text data fields. In MySQL, all text fields use one or the other string data type. As there are various types under String and each has its unique characteristics, it is essential to know which one is best for your requirement.
Apart from suitability, data type also defines the amount of data you can store in the database. Further, the storage location and the level of flexibility also get affected by the data type.
Therefore, knowing the different data types and their applicability is helpful for the smooth performance of everyday database operations.
The two most popular data types you should know about are VARCHAR and TEXT. So, let’s begin the investigation on which data type is best for you.
A Recent Development
These two data types can confuse you because, from MySQL 5.0.3 onwards, both VARCHAR and TEXT data types support the maximum length of 65,535 characters. Earlier, VARCHAR supported only 255 characters.
With this development, VARCHAR has become quite similar to TEXT, raising the debate about which is better. This has even made choosing one over another more challenging. So, let us make it simpler for you to choose; take a look into our thorough comparison but if you just need to rush through, below is the short answer:
VARCHAR is the Go-To option for most, but not for all
If you want to know which is better at the surface level, then VARCHAR is the way to go most times. It supports variable-length data to a maximum of 65,535 characters. Further, performance-wise it optimizes storage well, offers greater flexibility and, is fully indexable. However, VARCHAR is not always the right choice. What if you want to store data exceeding 65,535 characters? Read on to find out why VARCHAR is not always the go-to choice and when you should choose TEXT.
A Complete Comparison
Both VARCHAR and TEXT are a part of MySQL’s BLOB or Binary Large Object. However, they have their own unique characteristics too. Let’s understand each in isolation and then compare the two. But to fully understand VARCHAR, we should look at its origin, i.e., CHAR.
CHAR and VARCHAR:
CHAR is the predecessor to VARCHAR. It has the following features
- It supports the fixed length of 255 characters
*There is no need to declare the character count while creating the table - CHAR pads empty characters with spaces
- Storage consumption is fixed even if you consume less than 255 characters
VARCHAR, on the other hand, is Variable-CHAR with the following features:
It supports a maximum but not fixed length of 65,535 characters
You have to declare the maximum character count while creating the table
- There is no padding for unused characters
*It only uses the required amount of storage and 1-2 bytes more for the length prefix
The storage optimization and higher character limit make VARCHAR more flexible than CHAR.
TEXT and its variants
Now coming to TEXT, it is very similar to VARCHAR at the basic level, but it has two more variants, which sets it apart.
The three variants are:
| Variant | Maximum Length |
|---|---|
| TEXT | 65,535 bytes |
| MEDIUM TEXT | 16 MB |
| LONGTEXT | 4 GB |
Although TEXT is very similar to VARCHAR, its variants offer greater flexibility when data storage requirements exceed 65,535 bytes. However, there are some drawbacks too of using TEXT, so let’s compare it with VARCHAR.
VARCHAR vs. TEXT
Below is a parameter-wise comparison of the two data types:
Indexing Ability: VARCHAR can be fully indexed, while TEXT columns can be indexed only up to a certain length.
- Sorting Possibility: VARCHAR can be sorted using the entire length of the String, but this is not possible for TEXT
- Storage usage: TEXT occupies 2 + length of string storage space, while VARCHAR occupies 1 + length of string, up to 255 characters, and 2 + length of string greater than 255 characters. So, up to 255 characters, VARCHAR even uses lesser storage than TEXT.
- Performance Optimization: VARCHAR can be stored in MySQL’s memory storage; however, TEXT is not supported by it. So, if a query involves a TEXT column, temporary tables are created on the disk storage. Using disk-based tables takes a toll on the resources, and query run completion takes longer.
Verdict on which is better VARCHAR or TEXT
Looking at the above comparison, VARCHAR becomes the go-to choice for most web applications that require sorting text under 65,535 characters.
However, TEXT is still a reasonable choice if its limitations don’t create a problem for you, but there could be undesirable consequences of using TEXT, which gets eliminated in VARCHAR.
However, when more than 65,535 characters need to be used, MEDIUMTEXT and LONGTEXT are the best options.
Even so, VARCHAR is most frequently used because adding multi-gigabyte data into your website database is not advisable. It may have certain unintended consequences, and therefore, the need for using MEDIUMTEXT and LONGTEXT is not very common.
How to choose the right data type?
Although it is pretty clear that VARCHAR is best in most cases, but a blog post or a detailed explanation of a complaint on a website can easily exceed its character limit. With VARCHAR. You need to define the data type while creating the data table, so you might not want it to be restricted by the character limit. Therefore, MEDIUMTEXT and LONGTEXT should be chosen for such fields.
Below is an example of how to optimize the data types:
- Access the built-in tool of cPanel called phpMyAdmin
- Select your desired database from the list of databases of your website
- Check the structure of its tables to find out the data type
The above screenshot shows that the developers have used VARCHAR for shorter fields such as comment type, comment author, URL, etc. However, if you look at the second screenshot below, they use LONGTEXT for longer fields such as posts and comment content.
The two possible reasons for doing this are:
- The VARCHAR fields need to be indexed while LONGTEXT fields don’t need to be
- There is little chance of character limit exceeding in The VARCHAR fields, but in posts or comment content fields, the character limit can exceed the VARCHAR limit based on the nature of content that needs to be added.
So, to ensure that there is no later requirement of changing the data type, using a mix of VARCHAR and TEXT is advisable.
Conclusion
Both VARCHAR and TEXT have their own benefits and drawbacks, so no single data type fits all requirements. However, most of the database requirements can be met with VARCHAR, MEDIUMTEXT, and LONGTEXT should only be used where necessary and with caution.