Is it best to just use TEXT to store a description that may be long? I noticed that some forums encode their message before storing, is that optimal? what's the difference between TEXT and mediumtext?
From: http:// dev.mysql.com/doc/refman/5.0/en/string-type-overview.html A TEXT column with a maximum length of 65,535 (216 – 1) characters. A TEXT column with a maximum length of 16,777,215 (224 – 1) characters. What do you mean by 'encoding'? Into UTF-8? Encrypted? Gzip'ed? Compressing data using gzip within PHP can reduce plain text by an average of 80% which is staggering! If you don't need to do much/any analysis on that text (if you're just archiving it for instance) it is a really good technique for keeping the size of a database down.
not sure check phpbb3's database, not sure how they encoded it, but it's not stored as what the post is. btw, you said A TEXT column with a maximum length of 65,535 (216 – 1) characters. A TEXT column with a maximum length of 16,777,215 (224 – 1) characters. is one supposed to be mediumtext? or..?
Now would be a good time for another data-type consideration: http:// dev.mysql.com/doc/refman/5.0/en/storage-requirements.html A Mediumtext entails an extra byte of data per field. Seems insignificant, but it is worth knowing about. On a big database table, it could impact performance somewhat.
seems best to decide between text + medium text, 65,000/5 -> 13,000 words anyway, i think that would be fitting enough
As far as encoding goes, if you plan on having international traffic, with languages other then english, then you'll definitely want to encode your data as something like UTF8 to handle the additional characters. This is something you should plan out before hand, converting your data can give you alot of headaches later.