MySQL: Large VARCHAR vs. TEXT?

• TEXT and BLOB may by stored off the table with the table just having a pointer to the location of the actual storage. Where it is stored depends on lots of things like data size, columns size, row_format, and MySQL version.

• VARCHAR is stored inline with the table. VARCHAR is faster when the size is reasonable, the tradeoff of which would be faster depends upon your data and your hardware, you'd want to benchmark a real-world scenario with your data.

Can you predict how long the user input would be?

VARCHAR(X)

Max Length: variable, up to 65,535 bytes (64KB)
Case: user name, email, country, subject, password

---

TEXT

Max Length: 65,535 bytes (64KB)
Case: messages, emails, comments, formatted text, html, code, images, links

---

MEDIUMTEXT

Max Length: 16,777,215 bytes (16MB)
Case: large json bodies, short to medium length books, csv strings

---

LONGTEXT

Max Length: 4,294,967,29 bytes (4GB)
Case: textbooks, programs, years of logs files, harry potter and the goblet of fire, scientific research logging

There's more information on this question.

Just to clarify the best practice:

1. Text format messages should almost always be stored as TEXT (they end up being arbitrarily long)

2. String attributes should be stored as VARCHAR (the destination user name, the subject, etc...).

I understand that you've got a front end limit, which is great until it isn't. *grin* The trick is to think of the DB as separate from the applications that connect to it. Just because one application puts a limit on the data, doesn't mean that the data is intrinsically limited.

What is it about the messages themselves that forces them to never be more then 3000 characters? If it's just an arbitrary application constraint (say, for a text box or something), use a TEXT field at the data layer.