MySQL: VARCHAR or TEXT for long text fields

In this post:

• Varchar vs Text use cases
• Varchar vs Text maximum characters
• Varchar vs Text final notes
• Varchar vs Text general advises

Good design will save you time and money. But some decision are difficult even if you know the best practices and the rules. One of them is about storing long text in MySQL. Here are several notes on making this decision a bit easier.

Varchar vs Text use cases

• VARCHAR(X)

• Case: names , titles , emails(the mail of a person) , subjects (of message or mail), several sentences user input, links

• TEXT

• Case: messages, emails(text body), comments, formatted text, programming code, book chapters

• MEDIUMTEXT

• Case: json bodies, whole book, logs(medium size)

• LONGTEXT

• Case: extra long books, programs, huge log files, scientific research logging, machine learning data

Varchar vs Text maximum characters

Next table shows approximate number of characters. The number could depends based on encoding, language, used characters ..

Varchar vs Text final notes

• A table with TEXT or BLOB columns, can't be stored in memory - which could result in slower access to this columns ( about 3 4 times slower depending on the case)
• A UTF-8 column usually would be 3000-character varchar sized taking up to 9000 bytes(MySQL row length limit is 65,535 bytes )
• TEXT and BLOB columns are stored out of the table with the table pointing to the location of the actual storage.
• VARCHAR is stored inline. VARCHAR is faster ( reasonable size up to 1000 2000 characters)
• Text can use maximum of 21,844 characters if the charset is UTF8
• Text doesn't need to specify length(it will used as much as needed)
• VARCHAR needs length to be defined

Varchar vs Text advises

• Use TEXT column in a separate table which is only accessed when you actually need it.
• Queries against tables with TEXT columns should select only required fields and not all data per row(in case that you don't need this information)
• Carefully estimate your data and predict the storage needs
• Don't make the exception to be the rule - I know for auto-generated emails with 300 max characters. Do you think that is a normal case?