MySQL: BLOB/TEXT vs multiple VARCHAR columns

[rodry]

I have the following scenario (which you may be familiar with:

An online periodical is needed, similar in fashion to Slashdot and others. I suggested the best solution for it to be PERL/MySQL given the available resources ($).

Doing research I on development of similar sites I notice that one way to store an article/news text was to create several VARCHAR columns with the maximun of 255 characters for each one. The author would then break up the text to be posted into several chunks, 255 character long.

Then, upon reading the MySQL documentation in more detail, I noticed that it supports BLOB/TEXT column types that seemed to be specially suited for the kind of application I am dealing with.

So, the obvious question is, which is the preffered way to attack such problem. Or better yet, what are the pros and cons of each approach.

Thank you Monks.

Originally posted as a Categorized Question.

[athomason]

All string types are equivalent as far as your SQL is concerned; the only difference is how much data they can store. You can see a comparison of the size requirements of all the types here. Like you say, VARCHAR can only up to 255 characters. LONGTEXT can hold up to 4GB of data, assuming you have the available disk space. You're probably fine with a MEDIUMTEXT, which holds up to 16MB, and it will only cost you two more bytes per record than VARCHAR. And obviously, using larger fields frees you of worrying about artificially splitting and combining text.

If you're not doing this for the Perl experience but just want a working site, you can steal the GPL'ed Slashcode.

[lachoy]

Unlike many database systems out there, the overhead for BLOB/TEXT fields in MySQL (I normally just use TEXT) is practically none. Other databases (Sybase, IME) allocate at minimum a 2kb page for each TEXT field in a table, even if the TEXT field is empty. Eeek!

[cianoz]

Blob/TEXT fields have some limitation on mysql: (compared with VARCHAR)
have a look at the documentation for more details.