Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there a performance difference in MySQL between varchar sizes? For example, varchar(25) and varchar(64000). If not, is there a reason not to declare all varchars with the max size just to ensure you don't run out of room?

share|improve this question
1  
+1 this question applies similar to all DBMS. My observation many varchar sizes tend to grow. –  bernd_k Jan 8 '11 at 20:49
3  
Not MySQL, but this blog post by Depesz may answer your question for PostgreSQL. –  xenoterracide Jan 9 '11 at 3:18
add comment

5 Answers

You must realize the tradeoffs of using CHAR vs VARCHAR

With CHAR fields, what you allocate is exactly what you get. For example, CHAR(15) allocates and stores 15 bytes, no matter how characters you place in the field. String manipulation is simple and straightforward since the size of the data field is totally predictable.

With VARCHAR fields, you get a completely different story. For example VARCHAR(15) actually allocates dynamically up to 16 bytes, up to 15 for data and, at least, 1 additional byte to store the the length of the data. If you have the string 'hello' to store that will take 6 bytes, not 5. String manipulation must always perform some form of length checking in all cases.

The tradeoff is more evident when you do two things:
1. Storing millions or billions of rows
2. Indexing columns that are either CHAR or VARCHAR

TRADEOFF #1

Obviously, VARCHAR holds the advantage since variable-length data would produce smaller rows and, thus, smaller physical files.

TRADEOFF #2

Since CHAR fields require less string manipulation because of fixed field widths, index lookups against CHAR field are on average 20% faster than that of VARCHAR fields. This is not any conjecture on my part. The book MySQL Database Design and Tuning performed something marvelous on a MyISAM table to prove this. The example in the book did something like the following:

ALTER TABLE tblname ROW_FORMAT=FIXED;

This directive forces are VARCHARs to behave as CHARs. I did this at my previous job back in 2007 and took a 300GB table and sped up index lookups by 20%, without changing anything else. It worked as published. However, it did produce a table almost double in size, but that simply goes back to tradeoff #1.

You could analyze the data being stored to see what MySQL recommends for column definition. Just run the following against any table:

SELECT * FROM tblname PROCEDURE ANALYSE();

This will traverse the entire table and recommend column definitions for every column based on the data it contains, the minimum field values, maximum field values, and so forth. Sometimes, you just have to use common sense with planning CHAR vs VARCHAR. Here is a good example:

If you are storing IP addresses, the mask for such a column is at most 15 characters (xxx.xxx.xxx.xxx). I would jump right at CHAR(15) in a heartbeat because the lengths of IP addresses will not vary all that much and the added complexity of string manipulation controlled by an additional byte. You could still do a PROCEDURE ANALYSE() against such a column. It may even recommend VARCHAR. My money would still be on CHAR over VARCHAR in this instance.

CHAR vs VARCHAR issues can be resolved only through proper planning. With great power comes great responsibility (cliche but true)

share|improve this answer
2  
If you storing IP addresses I see no reason to store them as anything other than an int. That's all an IP address is. Many languages have some sort of ip2int function. If you want the convienence of a command line call it's not hard to make a stored procedure to convert A.B.C.D: A*pow(256,3)+b*pow(256,2)+c*256+d –  atxdba Oct 18 '11 at 2:50
1  
Err more to the point I guess mysql has it's own ip2int function: INET_ATON –  atxdba Oct 18 '11 at 3:08
    
@atxdba : The point of my answer is just using CHAR vs VARCHAR. I just use IP as an example because its string character size is closer to 15. Thus, rounding off a stable CHAR size in favor of VARCHAR is just an example for the sake of the question itself. Your commentary about better ways to represent IP addresses is quite valid and make the most sense. –  RolandoMySQLDBA Oct 18 '11 at 3:13
    
CHAR(15) allocates 15 characters, not bytes. For utf8, that is 45 bytes. –  Rick James Apr 5 '12 at 19:53
    
IP-addresses -- VARBINARY(39) is needed hold a human-readable IPv6 address. –  Rick James Apr 5 '12 at 19:54
add comment

The answer to this is actually rather complex. The short version: there is a difference.

  1. When creating temporary tables to filter results (e.g. GROUP BY statements), the full length will be allocated.

  2. The wire protocol (sending rows to the client) will likely allocate the larger length.

  3. The storage engine may/may not implement a proper varchar.

For (2) I admit the wire protocol is not something I am intimately familiar with, but the general advice here is try and apply at least some minimal effort to guess the length.

share|improve this answer
    
Worth pointing out. MySQL 5.7 can pack values in the sort buffer (variable length). Explained in more detail here: mysqlserverteam.com/… –  Morgan Tocker Feb 7 at 15:15
add comment

A varchar column that size makes queries on the entire table more likely to use temporary tables. According to the High Performance MySQL book. When the optimizer tries to see if it can run this query in memory or if it needs a temp table, it looks at the row size based on the table definition, meaning, for speed it does not try to see how much of the 64K characters you re actually using. This is why the writers recommend you not stretch out that definition way beyond the actual possible values that would go in the column. Obviously, if you set yourself up for more queries going into temp tables (even if the actual data size could fit in RAM) you have now incurred I/O penalties you could have avoided.

share|improve this answer
    
That's a very fresh perspective. If this is the book you are referring to (amazon.com/MySQL-High-Availability-Building-Centers/dp/…), please put the page number of the book in your answer, because I would like to read that. +1 !!! –  RolandoMySQLDBA Oct 19 '11 at 17:25
    
Silly me…High PERFORMANCE not availability: amazon.com/High-Performance-MySQL-Optimization-Replication/dp/…… page number is 236/237 It explains how generosity in defining a varchar column can be unwise. Keep in mind though that this book was written back when 5.1 was just out. A third edition is coming out next year to include all the BIG changes in 5.5 so maybe that will change :) –  TechieGurl Oct 20 '11 at 15:49
    
Page 236 mentions collation belonging to particular char sets. That could would be kind of nasty for VARCHAR. On page 237, Settings for client/server communications along with Figure 5-5 on page 238 show another reason. The process of translating character sets back and forth. Again, another nasty adventure for VARCHAR. –  RolandoMySQLDBA Oct 20 '11 at 15:59
    
To clarify, even though this section does not say outright that MySQL will go for create size, we know that when an operation needs a temporary table that table is in MEMORY Engine and THAT always stores string types in fixes chunks so that is how the generous definition can cause the needed MEMORY temp table to go to disk as opposed to staying in RAM –  TechieGurl Oct 20 '11 at 16:06
    
@RolandoMySQLDBA . Yep…that too…collation also becomes a factor here (esp if you use UTF-8 and do have non latin characters) and it all just kills ya when dealing with a memory engine table and leads to a speedier trip to disk –  TechieGurl Oct 20 '11 at 16:08
add comment

ensure you don't run out of room

This phrase implies that you ask the question because you're not sure about the data you'll be storing in the database. If that's true, you'll be well served to find out as soon as you can, because you'll need that for capacity planning. If you might be getting data elements with 7000 characters, for example, you need to know because that would have performance implications on any DBMS.

That said, I prefer to have column sizes related to the expected contents. For example, a phone number is unlikely to be longer than 50 characters, even if you include a country code and extension. Similarly, a zip or postal code will most likely be 20 characters or less.

share|improve this answer
    
Names and descriptions are where I've run into problems in the past. I've been on the user end of this as well, so I know how frustrating it is to run into a max length that was set by someone taking a guess when the system was designed. –  BenV Jan 9 '11 at 5:17
add comment

It's my understanding that the smaller fields may be includable in the index directly, whereas the longer ones cannot. Due to that limitation, if you want the strings to be indexable, I would say keep them shorter. Otherwise, no, being as how they're both varchar then ops like sorting or comparing will operate in like time, whether the fields are 25 or MAX.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.