3

I've begun to get very interested in the memory usage of MySQL. So I'm looking at this here:

http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

I get very excited about the prospect of saving memory by (for example) needing only a signed smallint where I was using an unsigned int in many places. Then I read about varchars...

"VARCHAR(M) - Length + 1 bytes if column values require 0 – 255 bytes"

What?! Now it appears to me as though storing a single varchar would use up so much memory, that I may as well not even get excited with my int vs. smallint because it's vastly overshadowed by the varchar field. So I come here asking if this is true, because it simply can't be? Are varchars really that terrible? Or should I really not be getting excited at all for my smallint discovery?

edit: Sorry! I should've been more clear. So, let's say I store a varchar with 7 characters, meaning 8 bytes. That means, then, that it uses the same as a number stored in a BIGINT column? That's what I'm concerned about.

Seth Goodwin
  • 107
  • 1
  • 1
  • 5
  • 1
    You probably misinterpreted documentation. Varchar(M) requires L+1 if column values requires 0-255 bytes where L - is the actual length. So if column is declared as `varchar(255)`,`latin1` charset and contains 'aaa', the storage length is 3+1 = 4. – a1ex07 Dec 26 '11 at 17:00
  • varchar stores the number of characters you've entered into it + one more byte to store the length, if you have a varchar(64) field and you place the string 'world peace' into it, this field will consume 12 bytes (11 for the string and 1 for the length). If the length is greater than 255 then two bytes are required to store it. On the other hand CHAR columns will always consume the size they were assigned (CHAR(16) field will always accupy 16 bytes while VARCHAR(16) will occupy 17 bytes only of you place a string 6 bytes long in it, if you place a 5 bytes string into it it will occupy 6). – Yaniro Dec 26 '11 at 17:06

2 Answers2

11

What this is saying is that for a given string length, the amount of storage used is equal to the length of the string in bytes, plus one byte to tell MySQL how long the string is.

So for instance, the word "automobile" is 10 bytes (1 for each character), so if it is stored in a varchar column it will take up 11 bytes. 1 for the number 10 , and 1 each for each of the characters in the string.

From the link you posted: http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

The storage requirements depend on these factors:

-The actual length of the column value

-The column's maximum possible length

-The character set used for the column, because some character sets contain multi-byte characters

For example, a VARCHAR(255) column can hold a string with a maximum length of 255 characters. Assuming that the column uses the latin1 character set (one byte per character), the actual storage required is the length of the string (L), plus one byte to record the length of the string. For the string 'abcd', L is 4 and the storage requirement is five bytes. If the same column is instead declared to use the ucs2 double-byte character set, the storage requirement is 10 bytes: The length of 'abcd' is eight bytes and the column requires two bytes to store lengths because the maximum length is greater than 255 (up to 510 bytes).

TetonSig
  • 2,189
  • 15
  • 21
  • Ultimately the edit was what people needed to read, but I will accept this for being as thorough as it should've been. Thank you. – Seth Goodwin Feb 03 '12 at 21:12
1

While I am no MySQL DBA, it appears there is a very simple answer to this question, and no need to go deeper into storage sizes - because it is NOT configureable.

Per MySQL memory storage documentation,

MEMORY tables use a fixed-length row-storage format. Variable-length types such as VARCHAR are stored using a fixed length.

Thus, you won't have any specific gains by using VARCHAR for a table using the MEMORY storage engine, no matter how VARCHAR is stored on other storage engines such as MyISAM or InnoDB.

ryanm
  • 2,239
  • 21
  • 31