Storing messages as BLOB (Binary Large Object) or ordinary text?

Question

What is the best way to store messaging texts in large/big web sites: BLOB or ordinary text in table or something else? Database: MySQL.

Answer 1

If by "messages" you mean something like SMS or IRC, stuff with a defined limit then I think VARCHAR is the most appropriate. If your messages can really be any size then TEXT would be better.

But I wouldn't have thought BLOB was suitable for anything except perhaps attachments (assuming your system has them). BLOBs are meant for binary files (e.g. images, Word documents) not pure text.

Answer 2

There are a lot of questions you need to ask yourself when making that decision. There's a very detailed article, To BLOB or not to BLOB, that is the question: Whether to store string in BLOB, or CHAR, or VARCHAR?, that talks about some of the things to consider.