Is it a waste not declaring a length?

[moose-en-a-gant]

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?

[kicken]

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).

[moose-en-a-gant]

Do I need to declare a length for a varchar?

 

If you say varchar(255) isn't that the max?

[Barand]

see

http://dev.mysql.com/doc/refman/5.6/en/string-types.html

[moose-en-a-gant]

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?

[kicken]

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.

[moose-en-a-gant]

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

[CroNiX]

VARCHAR won't allocate space for unused characters (stands for Variable Character), it's counterpart CHAR will.

[moose-en-a-gant]

Thanks for the clarification CroNiX