MySQL Unique Index Keylength

Question

I am hashing keys from one table that provides a 256 bit (64 char length) alphanumeric string that I intend to use in a second table as a foreign key.

The engine of choice here is InnoDB.

There are about 2 million entries in Table A with my unique-key 256 bit key.

Table B, with 7 million records, is related to table A with the 256 bit foreign key.

My question is, instead of 64 char. length keys, should I opt for 160 bit (40 digit) keys?

Will I see a large performance boost? Or the difference would not be too large?

My machine has limited computation power and although scripting and programming is quite easy, it would take me hours to compute and re-calculate for 160 bit from 256, which is the reason for my question.

Answer 1

Large PRIMARY KEYs and UNIQUE KEYs are a very bad idea for InnoDB. If you want a real performance boost for subsequent retrieval, you must use smaller PRIMARY KEYs. Why?

• The smaller the PRIMARY KEY, the better things will be
• Secondary indexes have a primary key associated with each secondary key. Bigger PRIMARY Key, bigger Secondary Index. and Vice-Versa
• Caching improves with smaller primary keys

Here is what you should do with 64-char keys

STEP01) Create a Key Table for 64-character keys

Start with a table that will hold the 64-character key and associate it with an auto incremented field

CREATE TABLE big64keytable
(
    bigkey   INT UNSIGNED NOT NULL AUTO_INCREMENT,
    big64key CHAR(64),
    PRIMARY KEY (bigkey),
    UNIQUE KEY (big64key)
) ENGINE=MyISAM;

STEP02) Create a Stored Function to Get numeric ID for the 64-character key

First, create the SQL to INSERT your 64-character key and then retrieve the numeric ID

SET @givenkey = '8g4gbf3g7b5gf4n3gfn8g927534';
INSERT IGNORE INTO big64keytable (big64key) VALUES (@givenkey);
SELECT bigkey FROM big64keytable WHERE big64key = @givenkey;

Now take this simple retrieval algorithm and place it in a Stored Function

DELIMITER $$

DROP FUNCTION IF EXISTS `GetBigKey` $$
CREATE FUNCTION `GetBigKey` (GivenKey CHAR(64)) RETURNS INT UNSIGNED
DETERMINISTIC
BEGIN

    DECLARE rv INT UNSIGNED;

    INSERT IGNORE INTO big64keytable (big64key) VALUES (GivenKey);
    SELECT bigkey INTO rv FROM big64keytable WHERE big64key = GivenKey;
    RETURN rv;

END $$

DELIMITER ;

Going forward, simply generate a numeric unsigned ID (4 bytes ) for each 64-character key.

Here are two of my past posts in the DBA StackExchange on other large PRIMARY KEY issues

• Dec 22, 2011 : File name as primary key?
• Oct 18, 2012 : MySQL: Unique constraint on large column