MySQL TEXT or VARCHAR

Question

We have a very large historical table that contains a column with at most 500 UTF8 characters, and the disk space grows really fast!

We're having at least 2 million rows a day... and we were wondering which would do a better job (mostly in storage but in performance as well)? TEXT or VARCHAR(512)?

Answer 1

From the MySQL manual:

In most respects, you can regard a BLOB column as a VARBINARY column that can be as large as you like. Similarly, you can regard a TEXT column as a VARCHAR column. BLOB and TEXT differ from VARBINARY and VARCHAR in the following ways:

There is no trailing-space removal for BLOB and TEXT columns when values

are stored or retrieved. Before MySQL 5.0.3, this differs from VARBINARY and VARCHAR, for which trailing spaces are removed when values are stored.

On comparisons, TEXT is space extended to fit the compared object,

exactly like CHAR and VARCHAR.

For indexes on BLOB and TEXT columns, you must specify an index

prefix length. For CHAR and VARCHAR, a prefix length is optional. See Section 7.5.1, “Column Indexes”.

BLOB and TEXT columns cannot have DEFAULT values.

http://dev.mysql.com/doc/refman/5.0/en/blob.html

Answer 2

VARCHAR is probably preferable in your case from both the storage and performance perspective. View this oft-reposted article.

Answer 3

This is useful information; I think in general, the answer is the varchar is usually the better bet.