What datatype should I use for my column? [MySQL]

views:

answers:

What datatype should I use for my column? [MySQL]

I am working on a content management system at work. I will have a column to hold the body of our users' content (HTML) and I'm not too sure what column type to use. I don't want to arbitrarily assign a maximum length but I don't know if using a LONGTEXT or BLOB field is overkill.

Maybe someone could give me a tip or point me in the direction of some sort of "best practices" article for these types of things? :)

Any help is appreciated

Use LONGTEXT so you can search it.

Vaidas Zilionis 2009-11-26 19:04:04

If searching is a requirement, this would be the best option.

Ben S 2009-11-26 19:07:45

Who is going to be searching through a nasty chunk of HTML?

ChaosPandion 2009-11-26 19:10:12

Since when does MySQL require the field to be LONGTEXT to search?

William 2009-11-26 19:14:10

You should GZip and Base64 encode the HTML then store it in a Text column.

ChaosPandion 2009-11-26 19:04:45

What advantage am I gaining from GZip? Would I do it for the compression or is there some other advantage that I am missing.

Gazillion 2009-11-26 19:18:10

I would say the compression is a HUGE advantage. You can cut your DB to web server transfer times in half (maybe exaggerated but there is a speed increase). Plus over time you will require a lot less storage space for the HTML.

ChaosPandion 2009-11-26 20:13:20

+2 A:

I recommend reading http://dev.mysql.com/doc/refman/5.0/en/string-type-overview.html so you know the size constraints of each field. Afterwards you can choose based off your understanding of the application if you should allow more.

Text A TEXT column with a maximum length of 65,535 (216 – 1) characters

Medium Text A TEXT column with a maximum length of 16,777,215 (224 – 1) characters.

Long Text A TEXT column with a maximum length of 4,294,967,295 or 4GB (232 – 1) characters.

I think you should set some kind of limit. It might be worth looking into what other software does (like Word Press) for example.

As for ChaosPandion: GZip is binary, if you're going to do that he should be using BLOB instead.

William 2009-11-26 19:07:20

I updated my answer with regards to your COMMENT.

ChaosPandion 2009-11-26 19:08:15

Also remember that the Text column (like the varchar column) has a variable length. Meaning that it only uses up the amount of space that is contained inside the field (unlike CHAR). The only space it uses is a 2 byte prefix so that the database knows the total size of the field.

William 2009-11-26 19:11:58

I have to ask, but why are you storing full html pages in the database? This is generally not a recommended solution...

Stephane Grenier 2009-11-26 19:27:08

We're not storing the whole page, just a part of the body that the client has access to modify on their own. Think of it like wordpress.

Gazillion 2009-11-26 19:28:11

Cool. I've just seen too many instances of the likes of TheDailyWTF. Just had to check :)

Stephane Grenier 2009-11-26 19:41:14

ansaurus

tags:

views:

answers:

What datatype should I use for my column? [MySQL]

related questions