Data Types mysql between varchar and mediumtext size

6 posts by 4 authors in: Forums > CMS Builder
Last Post: April 21, 2015   (RSS)

By Twocans - April 17, 2015

Hello,

I would be grateful for any input.

When setting values for my user input I have the option of varchar which is cool but only 255 char. Sometimes I need something max 2000 char but feel it is such a waste to have to set mediumtext as that is 16 odd meg and wowa, I do not want to give so much option. I see there is an option of setting other custom mysql column type and that gets me excited but having looked into it, in MySQL I am not really given much options. 

Is there a way of my setting a char type thus allowing me a max of 2000 char.

regards

Kenny

By Djulia - April 17, 2015

Hello,

Yes, you can use the option Other/Custom in the select MySQL Column Type :

TEXT

http://stackoverflow.com/questions/5053658/mysql-varchar2000-vs-text

Djulia

By gregThomas - April 17, 2015 - edited: April 17, 2015

Hey Twocans,

The easiest option would be to use mediumText, and then limit the users input to 2000 characters using the max length setting when editing a field. If you use the option suggested by Djulia, ensure that you set the max length to 2000 characters or you might get MySQL errors if a user enters too many characters.

Cheers,

Greg

Greg Thomas







PHP Programmer - interactivetools.com

By Twocans - April 18, 2015

Thank you,
Excuse my ignorance but when I read tutorials etc they never come across as too clear to me.

mediumText  states in the cms gui  max 16 meg.

My question is

Were I to use datatype as mediumText and set max char size to 2000, does this mean every time a record is created I use up 16 meg weight of the data base space?

Or

By setting mediumText and setting max char size to 2000 does that mean, when ever a record is created I use up to 2000 char space/weight of my database?

Sometimes these things are not so clear, and no matter what I search and read etc they never give a straight forwardish answer.

I have very grateful to you both and others for any input.

cheers

Kenny

By Dave - April 20, 2015

Hi Kenny, 

You can usually safely ignore all of this and just go with the defaults on typical websites with negligible performance differences, but here's some info for you:

The "Max Length" setting in the CMS is for error checking only.  It doesn't change how the data is stored in MySQL, it just gives an error when you submit a string longer than that.

When you specify a varchar field you can specify the mysql column length, but for mediumtext you can not.  

You can find the "Storage Requirements for String Types" in the MySQL docs here:
http://dev.mysql.com/doc/refman/5.5/en/storage-requirements.html#idm140193328410032

And to answer your question, every mediumText column uses the number of bytes of the data entered plus 3 more.  So "Hello World" is 11 bytes and would take 14 bytes of disk space (11 + 3), not 16 megs.

And 97% of the time, if you optimize last when you experience problems and don't worry about it at all, that will actually work better.  See: http://c2.com/cgi/wiki?PrematureOptimization

Hope that helps!

Dave Edis - Senior Developer
interactivetools.com