Why would I use VARCHAR over TEXT in MySQL?

Question

Hi,

I noticed that in MySQL, both VARCHAR and TEXT offer variable-sized data. Well, VARCHAR is a bit more efficient in data storage, but still, TEXT MEDIUMTEXT and LONGTEXT offer a lot more potential. So, what are the real uses of VARCHAR?

Answer 1

First of all, you should read the 10.4. String Types section of the MySQL's manual : it'll give you all the informations you are looking for :

• 10.4.1. The CHAR and VARCHAR Types
• 10.4.3. The BLOB and TEXT Types

A couple of important differences :

• Difference in the amount of text those can contain :
  • varchar have a quite small size limit ; with the newest versions of MySQL, it's 64 KB, for the total of all varchar columns of a row -- which is not that much.
  • TEXT have virtually no limit, as they can contain something like 2^32 bytes.
• There are differences in indexing and sorting, if I'm not mistake ; quoting the page about TEXT :
  • About sorting : "Only the first max_sort_length bytes of the column are used when sorting."
  • And, about performances : "Instances of BLOB or TEXT columns in the result of a query that is processed using a temporary table causes the server to use a table on disk rather than in memory"

Considering these informations, if you are sure that your strings will not be too long, and that you'll always be able to store them in a varchar, I would use a varchar.