I have an indexed column that stores an MD5 hash. Thus, the column will always store a 32-character value. For whatever reason, this was created as a varchar rather than a char. Is it worth the trouble of migrating the database to convert it to a char? This is in MySQL 5.0 with InnoDB.

  • 8
    WARNING This question and its answers were written before InnoDB and utf8 were the defaults.
    – Rick James
    Commented Jul 2, 2016 at 5:18

3 Answers 3


A similar question was asked before

Performance implications of MySQL VARCHAR sizes

Here is the excerpt of my answer

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:


This directive forces all 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:


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


When it comes to MD5, the computation of strlen internally should be eliminated when switching the entire row format. There would be no need to change the field definition.

If the MD5 key is the only VARCHAR present, I would go for it and convert the table row format to fixed. If there is a signifcant number of other VARCHAR fields present, they would benefit as well. In exchange, the table would expand to about twice its size. But queries should accelerate about 20% more without additional tuning.

  • 1
    I think I'd use a char(4) or something like an unsigned integer for an IP address Commented May 10, 2011 at 21:28
  • @JackPDouglas You are correct on that one that point. Commented May 10, 2011 at 21:36
  • Aren't indexes stored with a fixed length anyway? I don't get how changing the storage format to fixed length improved index lookups. Do you mean it improved table scans? Commented Apr 15, 2012 at 22:24
  • 1
    @JackDouglas, Why not bit and binary?
    – Pacerier
    Commented Dec 10, 2014 at 3:46
  • @Pacerier that would be better, I agree :) Commented Dec 10, 2014 at 5:32

It looks like you will save 1 byte per value or about 3% by converting to a char. Probably not worth it if you are storing MD5 in hex anyway - you could save 50% by using a binary instead.

Thanks to Ovais (see comments) for pointing out that char(32) can use a lot more than 32 bytes if you are using a multibyte character set.

Thanks to Rick James for pointing out that you should use the unhex function to convert the a hex string to binary:

create table foo(bar varbinary(100));
insert into foo(bar) values(md5('a')); 
insert into foo(bar) values(unhex(md5('a'))); 
select length(bar) from foo;
| length(bar) |
| ----------: |
|          32 |
|          16 |

db<>fiddle here

  • Good call on changing to binary.
    – RThomas
    Commented May 10, 2011 at 21:17
  • I'm planning on converting this into a binary. Now that I think about it though, the size shouldn't be any different just based on whether I'm using a byte or a char since our encoding is utf-8. Or am I wrong? Commented May 17, 2011 at 17:36
  • @Jason - encoding doesn't apply to binary - or have I misunderstood? Commented May 17, 2011 at 18:53
  • 3
    for a char(32) column with a character set of utf-8, every value would need 32x3 bytes for storage. Why would you need to set the MD5 hash value to be utf-8. Converting to binary(32) would need 32 bytes per value. Commented May 25, 2011 at 12:05
  • 2
    Changing to BINARY does very little unless you also use UNHEX(). That is, you can store UNHEX(MD5(x)) into a 16-byte BINARY(16) to save significant space over storing MD5(x) into CHAR(32) CHARACTER SET ascii.
    – Rick James
    Commented Aug 16, 2018 at 5:11

It is not worth changing in my opinion. If you look through the documentation here it should illustrate the difference between the two. In your usage scenario the one doesn't really offer any significant benefit over the other unless you are really concerned about the extra bit of overhead related to row size.


Also note the first comment on the documentation I link to above... "CHAR will only speed up your access if the whole record is fixed size. That is, if you use any variable size object, you might as well make all of them variable size. You gain no speed by using a CHAR in a table that also contains a VARCHAR"

  • That "speedup" applies to MyISAM, not InnoDB.
    – Rick James
    Commented Jul 2, 2016 at 5:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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