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.

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.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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

share|improve this answer
    
Hello Ronaldo. This post is extremely professional and an excellent resource. I kindly thank you for this wonderful work. It has helped me tremendously. –  Phil Oct 22 '12 at 18:41
    
Thank you accepting my answer, and you are welcome. –  RolandoMySQLDBA Oct 22 '12 at 18:45
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.