database - VARCHAR vs TEXT in MySQL

Question

I have two fields: one to store an excerpt with a max size of 500 characters, and another to store a description with a max size of 10,000 characters.

What data types should I use, TEXT or VARCHAR? And why?

After MySQL 5.0.3 VARCHAR accepts ~65000 characters. But this does not tell why I should use one type and or the other.

I'm reasoning that I should use VARCHAR for the excerpt because I can assign a size limit, and TEXT for the description field as it's larger.

Answer 1

A long VARCHAR is stored in the same manner as a TEXT/BLOB field in InnoDB (which I assume you're using for transactionality, referential integrity and crash recovery, right?) - that is, externally to the rest of the table on disk (which may require another disk read to retrieve).

From storage prospective BLOB, TEXT as well as long VARCHAR are handled same way by Innodb. This is why Innodb manual calls it “long columns” rather than BLOBs.

source

Unless you need to index these columns (in which case VARCHAR is much faster) there is no reason to use VARCHAR over TEXT for long fields - there are some engine specific optimisations in MySQL to tune the data retrieval according to length, and you should use the correct column type to take advantage of these.

In case you're using MyISAM an in-depth discussion on the topic is here.