In and Around the Coding World

Innvo.com

Tip: Storing MD5 Values

^{Page last updated on 2011 / 16 / 08}

Use 16 bytes, not 32

A common occurrence I have noticed in MySQL apps is that MD5 values are stored as 32 byte values rather than 16. Just to 're-hash', an MD5 value is a 16 byte hexadecimal value, typically used as a unique fixed-length signature of a string, useful for identifying unique strings or one-way encryption of passwords.

When represented as a non-binary string an MD5 value is 32 bytes and often stored as a fixed-length 32 byte CHAR column in a database. Each of the 32 characters range from 0 to F, essentially 16 values. These characters can be 'crunched' from 2 bytes to 1 by merging 2 hex characters into one binary character, reducing the hash size to 16 bytes. e.g. 16x16 = 256 values = 1 byte.

MySQL has some handy functions to convert between binary and text representations of an MD5, namely HEX() and UNHEX() which consecutively turn binary to hexadecimal values and vice versa.

Essentially, it is more advantageous to use a BINARY (16) column than a CHAR (32) column for storing MD5 values.

Why, you ask? Because the field size is smaller, searches upon it will be quicker, as shown in this benchmark below.

Testing 32 byte CHAR versus 16 byte BINARY column

Step 1: Create 2 tables, one storing 32 byte MD5 hex values, the other storing 16 byte binary values.

CREATE TABLE IF NOT EXISTS `md5values16`
  (
     `hash` BINARY(16) NOT NULL,
     UNIQUE KEY `hash` (`hash`)
  )
ENGINE=myisam;
 
CREATE TABLE IF NOT EXISTS `md5values32`
  (
     `hash` CHAR(32) NOT NULL,
     UNIQUE KEY `hash` (`hash`)
  )
ENGINE=myisam;  
 

Step 2: Populate the tables with data

<?php
 
// Filling the tables with 1 million values
 
    mysql_connect('localhost','root','root');
    mysql_select_db('test');
 
    for($i = 1;$i < 1000000;$i+= 10000)
    {
    echo $i,' ';
    $array = array(array(),array());
        for($j = $i;$j < $i + 10000;$j++)
        {
        $array[0][] = '(MD5(\''.$j.'\'))';
        $array[1][] = '(UNHEX(MD5(\''.$j.'\')))';
        }
    mysql_query('INSERT INTO md5values32 VALUES '.implode(',',$array[0])) or die('2. '.mysql_error());
    mysql_query('INSERT INTO md5values16 VALUES '.implode(',',$array[1])) or die('1. '.mysql_error());
    }
 
?>
 

Step 3: Add a UNIQUE index to the tables and load the indexes into memory

ALTER TABLE `md5values16` ADD UNIQUE (`hash`);
ALTER TABLE `md5values32` ADD UNIQUE (`hash`);
LOAD INDEX INTO CACHE `md5values16`;
LOAD INDEX INTO CACHE `md5values32`;  
 

Step 4: Benchmark the tables for comparison, using a random 100,000 queries

<?php
 
// Benchmark tables
    mysql_connect('localhost','root','root');
    mysql_select_db('test');
 
 
function microtime_float()
{
list($usec, $sec) = explode(" ", microtime());
return ((float)$usec + (float)$sec);
}
 
$time_start = microtime_float();
    for($i = 0;$i < 100000;$i++)
    {
    $rand = rand(1,1000000);
    mysql_query('SELECT hash FROM md5values32 WHERE hash = MD5(\''.$i.'\')');
    }
echo (microtime_float() - $time_start)." seconds for 16 byte column\n";
 
$time_start = microtime_float();
    for($i = 0;$i < 100000;$i++)
    {
    $rand = rand(1,1000000);
    mysql_query('SELECT hash FROM md5values16 WHERE hash = UNHEX(MD5(\''.$i.'\'))');
    }
echo (microtime_float() - $time_start)." seconds for 32 byte column\n";
 
?>
 

The Result

1st Pass
12.945791006088 seconds for 16 byte column
19.781244039536 seconds for 32 byte column

2nd Pass
12.882713079453 seconds for 16 byte column
20.835686922073 seconds for 32 byte column

3rd Pass
12.871006011963 seconds for 16 byte column
20.780813932419 seconds for 32 byte column

Previous Article
MySQL Partitioning and Its UsesNext Article
Loading MyISAM Tables Into Memory

© Innvo.com | Privacy