moose-en-a-gant Posted February 4, 2015 Share Posted February 4, 2015 Say you aren't sure how long a value is, but it's not more than 10 characters and you use text instead of varchar(10) just because you are unsure, is that a waste? I mean is "empty space" allocated by specifying a length or what? Quote Link to comment Share on other sites More sharing options...
kicken Posted February 4, 2015 Share Posted February 4, 2015 It'd be inefficient to use TEXT. Data for a TEXT column is stored separately from the main record, so when you need to read that data the database will first have to locate which records need to be read, then separately locate and read the actual value for a text column. VARCHAR/CHAR values are store in the main record directly so there is no need for that second read operation. Essentially you should stick to VARCHAR and only use TEXT for values for which you want to allow an exceptionally large value. In modern Mysql versions a VARCHAR will work for up to 65535 bytes (subject to various conditions). 1 Quote Link to comment Share on other sites More sharing options...
moose-en-a-gant Posted February 4, 2015 Author Share Posted February 4, 2015 Do I need to declare a length for a varchar? If you say varchar(255) isn't that the max? Quote Link to comment Share on other sites More sharing options...
Barand Posted February 4, 2015 Share Posted February 4, 2015 see http://dev.mysql.com/doc/refman/5.6/en/string-types.html Quote Link to comment Share on other sites More sharing options...
moose-en-a-gant Posted February 4, 2015 Author Share Posted February 4, 2015 I'm still unclear on this, If I only use 17 characters and I declared varchar(255) or anything more than 17 like varchar(30) is that a waste? Is that length allocated? Should it be exact? I don't know exactly the number that is generated from my passwordhashing with an array cost of 9, I think it is fixed I just haven't counted each one yet, could just paste it into a character counter but if I use varchar(255) to be safe, is that fine or am I wasting space wtih the unused space? Quote Link to comment Share on other sites More sharing options...
kicken Posted February 4, 2015 Share Posted February 4, 2015 See the page on Storage Requirements for the details of how data is stored and how much space is required for each data type. VARCHAR(M), VARBINARY(M) L + 1 bytes if column values require 0 – 255 bytes, L + 2 bytes if values may require more than 255 bytes So if you have a VARCHAR(30) but only store a 10-byte string, then the actual space required is 11 bytes (1 (length) + 10 (data)). VARCHAR(255) is a good generic if you're not sure how long a particular string might be. Quote Link to comment Share on other sites More sharing options...
moose-en-a-gant Posted February 4, 2015 Author Share Posted February 4, 2015 So the remaining 19, that's nothing then. Doesn't count as "allocated space" ? Also, the letter a, is that a byte? 8 bits = 1 byte but... I'll ready on this Thanks for the clarification I'll use varchar255 for potentially long entries and text for paragraphs / lots of text Quote Link to comment Share on other sites More sharing options...
CroNiX Posted February 4, 2015 Share Posted February 4, 2015 VARCHAR won't allocate space for unused characters (stands for Variable Character), it's counterpart CHAR will. Quote Link to comment Share on other sites More sharing options...
moose-en-a-gant Posted February 5, 2015 Author Share Posted February 5, 2015 Thanks for the clarification CroNiX Quote Link to comment 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.