“Should I use TEXT or VARCHAR field here?”

I’ve lost count of the number of times that I asked myself this question when putting together database structures. Since the maximum a VARCHAR can hold is 255, it becomes a question of whether or not the data you’re saving will be any longer than that. Sometimes that’s an easy call (phone number = VARCHAR; email body = TEXT), other times its blurry (verbose error logs, foreign-language data sets, user-submitted comments, etc).

“So what? Why not just use TEXT and be done with it?"

It’s true that in most cases it won’t make a difference. However, if you need to index and search the field, you should think carefully before blindly using TEXT. The data in TEXT type fields are stored outside the table itself, using only a few bytes for pointer information. This means that TEXT fields are not indexed, while VARCHAR fields are. This can have a tremendous effect on your SQL query speeds, as generally larger TEXT fields increase query time exponentially. Even if we take indexing out of the picture, the external storage of TEXT fields means that you’ll still see generally faster searches with VARCHAR.

Which brings us back to the original problem: when is a 255 character cap good enough? See for yourself. Below you’ll find a block of lorem ispum text that’s exactly 255 characters long (spaces count):

Lorem ipsum dolor sit amet, nonummy ligula volutpat hac integer nonummy. Suspendisse ultricies, congue etiam tellus, erat libero, nulla eleifend, mauris pellentesque. Suspendisse integer praesent vel, integer gravida mauris, fringilla vehicula lacinia non

If you’re like me, you’ll look at that and say, “That’s a lot more than I thought.”

Another way to look at it: RFC2822 says that a subject line may contain 998 total characters, with a max of 78 per line. Most mail clients don’t support multi-line subjects, so 78 characters is the practical limit you’ll find in most cases.

So if you ever find yourself doing that fuzzy-string-length-guestimation math in your head, bookmark this page to add a visual to the guesswork as well.