What are the optimum varchar sizes for MySQL?

mysql varchar
ae. picture ae. · Jul 20, 2009 · Viewed 45.3k times · Source

How does MySQL store a varchar field? Can I assume that the following pattern represents sensible storage sizes :

1,2,4,8,16,32,64,128,255 (max)

A clarification via example. Lets say I have a varchar field of 20 characters. Does MySQL when creating this field, basically reserve space for 32 bytes(not sure if they are bytes or not) but only allow 20 to be entered?

I guess I am worried about optimising disk space for a massive table.

Answer

Kris Erickson picture Kris Erickson · Jul 20, 2009

To answer the question, on disk MySql uses 1 + the size that is used in the field to store the data (so if the column was declared varchar(45), and the field was "FooBar" it would use 7 bytes on disk, unless of course you where using a multibyte character set, where it would be using 14 bytes). So, however you declare your columns, it wont make a difference on the storage end (you stated you are worried about disk optimization for a massive table). However, it does make a difference in queries, as VARCHAR's are converted to CHAR's when MySql makes a temporary table (SORT, ORDER, etc) and the more records you can fit into a single page, the less memory and faster your table scans will be.

Related questions

#1071 - Specified key was too long; max key length is 767 bytes

When I executed the following command: ALTER TABLE `mytable` ADD UNIQUE ( `column1` , `column2` ); I got this error message: #1071 - Specified key was too long; max key length is 767 bytes Information about column1 and column2: column1 varchar(20) utf8_general_ci column2 …

Read More
What is the MySQL VARCHAR max size?

I would like to know what the max size is for a MySQL VARCHAR type. I read that the max size is limited by the row size which is about 65k. I tried setting the field to varchar(20000) but it …

Read More
MySQL: Large VARCHAR vs. TEXT?

I've got a messages table in MySQL which records messages between users. Apart from the typical ids and message types (all integer types) I need to save the actual message text as either VARCHAR or TEXT. I'm setting a front-end …

Read More