[SOLVED] MySQL Memory Usage on Text data types

[theinfamousmielie]

Hey all,

 

I'm trying to find out which is the most memory efficent (and therefore server-friendly) datatype to use for a text field that could have between 1 and several million characters. It's for content storage on a content-management system, so the majority of information would probably be less than 65535 characters (page titles, headings, etc) but i still need to be able to store large amounts of data if need be.

 

Currently using 'MEDIUMTEXT'. total overkill for now ... but i'm trying to futureproof and cover all bases.

 

Any ideas?

[Vikas Jayna]

Think this is the best solution possible. As such the difference between mediumtext and text is only the extra byte used by mediumtext to store the length of the string and hence there is hardly any overhead of declaring the column as mediumtext

[theinfamousmielie]

Thats what i thought too ... hehe.

 

Thanks for the help

[fenway]

If you're using MYSQL5, stick with VARCHAR, not TEXT.

[theinfamousmielie]

Heya fenway!

 

Yeah, but how far does varchar spread in 5? I'm still in the 'mysql 4' frame of mind (shame on me, i know) where i use varchars up to 255 characters, then text. Lol. But i'm changing that now. Can a mysql5 varchar compare to mediumtext? surely not?

[fenway]

VARCHAR goes up to 65K.

[theinfamousmielie]

Hi Fenway,

 

Aaah okay. Well you learn something (simple) every day! hehehe.

 

Unfortunately, while the majority of data going into that field will be well under 65k characters, there will be a need to store more information occasionally, perhaps a very long html page, or large XML document. Is the benefit gained so great that i should essentially say 'tough luck' to content larger than 65k characters? or should i stick with MEDIUMTEXT ?

 

[fenway]

It's just that MySQL handles TEXT fields very poorly... if you must have them, consider have them in a separate table, or at the very least, not retrieving them with * ever.

[theinfamousmielie]

Hey fenway,

 

Thanks for the advice. I'm going to make it a varchar for now. With a little luck, nobody will need more than 65k characters ever and the problem will be a non-issue.

[fenway]

Just remember that even though VARCHAR is better than TEXT, any temporary table operation (e.g. filesort from grouping/ordering, subqueries) will silent convert VARCHAR to CHAR, so it will take up the maximum amount of bytes!  So use the "full length" with caution

[theinfamousmielie]

Won't be any of those

 

Essentially, it's for a content management / templating type system.

 

The main table, which contains the layout and other information (the Content) table has reference to a layout file and menu items and where it sits in the site tree etc etc. The table that holds the VARCHAR/TEXT field in question is almost like an interlinking table so as such we're referencing it by an ID 99% of the time. So this table has three columns. ContentID (which references it to a particular 'page'), FieldVariable (which contains the variable used in the template which will be searched for and replaced with the actual data) and FieldData ... which is the field in question.

 

My replacement query will be something like "SELECT FieldData FROM _ContentData WHERE ContentID = 1 AND FieldVariable = '##REPLACEME##' " - which should be quick and clean, regardless of data type.

 

Having said that, this may not be the most efficient way of doing it ... it's still very much in prototype. But does it make sense, and with that in mind, does the memory thing even matter? (the only time the FieldData column is referenced is in a FullText search if needed)

[fenway]

Then you'll probably be ok.

[theinfamousmielie]

alrighty, thanks mate