TEXT vs VARCHAR in InnoDB MySQL 5.5. When to use each one

Question

What are the main differences between text and varchar in InnoDB storage engine at MySQL 5.5?

Is text or varchar(5000) used if we are talking about a variable text field of not more than 5000 chars?

Answer 1

According to a forum post in innodb.com

From InnoDB's perspective varchar, text and blob are laid out on the disk in exactly same way. If you are interested in the internal details, there is a difference in the way InnoDB passes the varchar and text values back to MySQL. In case of varchar the value is passed back inside the row buffer whereas in case of text the row buffer contains a pointer to a chunk of memory containing actual value. But again this is more like a detail of MySQL storage engine interface and should be irrelevant to the end user.

Also check this.

Answer 2

Generally speaking the usage of TEXT was to "attach" infrequently-used, large data to a row with minimal performance impact when it's not being accessed, e.g. you wouldn't want to search/index the contents. It was also the only option above the old 255 byte max for VARCHARs.

However InnoDB has blurred the once clear line between VARCHAR and TEXT. The three main differences I can find are:

1. One remaining performance difference is that TEXT fields force temporary tables to disk during SELECT statements. This can be a performance hit for smaller data, but see this post for how using really big VARCHARs in this scenario might be worse due to their conversion to fixed-size in MEMORY storage. ie. large VARCHARS will fill up the memory buffers and may still go to disk.

2. BLOB and TEXT columns cannot have DEFAULT values. reference

3. Only the first N chars of TEXT fields are indexed. reference

So while its true they're now stored in the same structure, it's always worth benchmarking your entire app to see what other related differences may affect performance, sorting, search, storage size, packet size, backups/replication etc.