One of the changes in MySQL version 5.0.3 included an increase to the maximum length of VARCHAR fields from 255 to 65,535 characters. That made the VARCHAR type more similar to TEXT than ever before. For those of us who design database tables, choosing between VARCHAR and TEXT now became more challenging as a result. In today's blog, we'll outline the key differences between the two and layout the factors to consider when deciding which data type to go with.
Some Differences Between VARCHAR and TEXT
While both data types share a maximum length of 65,535 characters, there are still a few differences:
- The VAR in VARCHAR means that you can set the max size to anything between 1 and 65,535. TEXT fields have a fixed max size of 65,535 characters.
- A VARCHAR can be part of an index whereas a TEXT field requires you to specify a prefix length, which can be part of an index.
- VARCHAR is stored inline with the table (at least for the MyISAM storage engine), making it potentially faster when the size is reasonable. Of course, how much faster depends on both your data and your hardware. Meanwhile, TEXT is stored off table with the table having a pointer to the location of the actual storage.
- Using a TEXT column in a sort will require the use of a disk-based temporary table, as the MEMORY (HEAP) storage engine.
TEXT Types
Should you require the TEXT type, know that there are actually three flavors; in addition to TEXT, there are also MEDIUMTEXT or LONGTEXT varieties. The latter two are for storing textual content that is longer than 65,535 characters. MEDIUMTEXT stores strings up to 16 MB, and LONGTEXT up to 4 GB! It should go without saying that you should avoid using these larger types unless you have a lot of storage space.
Selecting VARCHAR and TEXT Types in Navicat
In both Navicat for MySQL and Navicat Premium, the Object Designer allows you to create and maintain all sorts of database objects, including Tables, Views, Functions, Indexes, and, of course, columns. Under the Type header, you can select a column's data type simply by selecting it from a drop-down. As you can see, it contains the text, mediumtext, and longtext types:
As for the VARCHAR type, you can also select it from the Type drop-down, but then you should edit the Length value if you want a value other than 255 (the default).
TIP: Since TEXT fields can get quite long, Navicat has a FORM view that give them more room:
Conclusion
The take-away we can draw from all of this is that one should use a VARCHAR field instead of TEXT for columns between 255 and 65k characters if possible. That will lead to potentially less disk reads and less writes.
Interested in finding out more about Navicat for MySQL or Navicat Premium? You can try both for 14 days completely free of charge for evaluation purposes!
