theinfamousmielie Posted October 7, 2008 Share Posted October 7, 2008 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? Quote Link to comment https://forums.phpfreaks.com/topic/127363-solved-mysql-memory-usage-on-text-data-types/ Share on other sites More sharing options...
Vikas Jayna Posted October 7, 2008 Share Posted October 7, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/127363-solved-mysql-memory-usage-on-text-data-types/#findComment-658919 Share on other sites More sharing options...
theinfamousmielie Posted October 7, 2008 Author Share Posted October 7, 2008 Thats what i thought too ... hehe. Thanks for the help Quote Link to comment https://forums.phpfreaks.com/topic/127363-solved-mysql-memory-usage-on-text-data-types/#findComment-658923 Share on other sites More sharing options...
fenway Posted October 7, 2008 Share Posted October 7, 2008 If you're using MYSQL5, stick with VARCHAR, not TEXT. Quote Link to comment https://forums.phpfreaks.com/topic/127363-solved-mysql-memory-usage-on-text-data-types/#findComment-659110 Share on other sites More sharing options...
theinfamousmielie Posted October 7, 2008 Author Share Posted October 7, 2008 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? Quote Link to comment https://forums.phpfreaks.com/topic/127363-solved-mysql-memory-usage-on-text-data-types/#findComment-659143 Share on other sites More sharing options...
fenway Posted October 11, 2008 Share Posted October 11, 2008 VARCHAR goes up to 65K. Quote Link to comment https://forums.phpfreaks.com/topic/127363-solved-mysql-memory-usage-on-text-data-types/#findComment-662868 Share on other sites More sharing options...
theinfamousmielie Posted October 13, 2008 Author Share Posted October 13, 2008 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 ? Quote Link to comment https://forums.phpfreaks.com/topic/127363-solved-mysql-memory-usage-on-text-data-types/#findComment-663805 Share on other sites More sharing options...
fenway Posted October 13, 2008 Share Posted October 13, 2008 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. Quote Link to comment https://forums.phpfreaks.com/topic/127363-solved-mysql-memory-usage-on-text-data-types/#findComment-664233 Share on other sites More sharing options...
theinfamousmielie Posted October 14, 2008 Author Share Posted October 14, 2008 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. Quote Link to comment https://forums.phpfreaks.com/topic/127363-solved-mysql-memory-usage-on-text-data-types/#findComment-664795 Share on other sites More sharing options...
fenway Posted October 14, 2008 Share Posted October 14, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/127363-solved-mysql-memory-usage-on-text-data-types/#findComment-664942 Share on other sites More sharing options...
theinfamousmielie Posted October 14, 2008 Author Share Posted October 14, 2008 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) Quote Link to comment https://forums.phpfreaks.com/topic/127363-solved-mysql-memory-usage-on-text-data-types/#findComment-664963 Share on other sites More sharing options...
fenway Posted October 14, 2008 Share Posted October 14, 2008 Then you'll probably be ok. Quote Link to comment https://forums.phpfreaks.com/topic/127363-solved-mysql-memory-usage-on-text-data-types/#findComment-665274 Share on other sites More sharing options...
theinfamousmielie Posted October 15, 2008 Author Share Posted October 15, 2008 alrighty, thanks mate Quote Link to comment https://forums.phpfreaks.com/topic/127363-solved-mysql-memory-usage-on-text-data-types/#findComment-665983 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.