Using the binary vs. char type in MySQL with PHP

In regards to using BINARY over CHAR, while this might be splitting hairs in terms of performance, the BINARY option is going to be more efficient in multiple fronts. The reason is that if you store an MD5 as a string, it requires 8 bits per character. But you are actually only representing 4 bits per character. If you store the string in binary, you can half the size of the field. It is also going to make the index smaller and more efficient since there is no need to compare strings - you are merely comparing numbers.

Unfortunately, to do this requires, some making some modifications to both MySQL and your application. First, you would need to update your existing data in MySQL. To do that, you could do something like:

mysql> CREATE TABLE newtable like oldtable;
mysql> INSERT INTO newtable SELECT UNHEX(md5field), field2, field3, ..., fieldN;

Then, you need to update your application. If you are using PHP, you can use the MD5 function:

$binaryMD5 = md5('my string', true);

The 'true' in the md5 function call tells PHP to return the binary version of the MD5 instead of string-based hex representation. You can then insert the data into MySQL as you did before, although since it is binary data, you may want to look at using prepared statements and the MySQLi extension.

So, as you can see, while it is not trivial, it's not terribly difficult, but is something you would absolutely need to test first before moving into production. It should give you better performance and lower your disk usage though so it something worth looking at. Some food for thought if nothing else.