Mysql – compouned (composite) unique keys

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

Short version:

CREATE TABLE Relation
( stu_id INT NOT NULL REFERENCES Student,
  par_id INT NOT NULL REFERENCES Parent,
  PRIMARY KEY (stu_id, par_id)
) ;

Long version:

• Why use short forms for names, like stu_id and par_id? Why not student_id? Saving typing 3-4 characters? How will you differentiate between parent_id and parameter_id? Or school_id and schoolmaster_id?

• The name "Relation" is not very descriptive for a relationship. (Note also that in relational model terminology, "relation" has a meaning very close to "table".) I couldn't come with a good name though, so we could use "Guardian" or "Student_Parent" (this combination is often used in intersection tables)

• The short version above is just an example. While it is working, it uses a lot of shortcuts, like the inline references. It's far better in my opinion, to name all constraints and declare all (primary, unique, foreign key and check) constraints after the column declarations, like in the long version below.

• It's also good to choose some naming conventions anduse them consistently in all the tables, e.g. Tablename_PK for the primary keys, ReferencedTable_referencingTable_FK for the foreign keys, Something_UQ for the unique constraints, etc.

CREATE TABLE Guardian
(                                        -- columns
  student_id INT NOT NULL, 
  parent_id INT NOT NULL,
                                         -- constraints
  CONSTRAINT Guardian_PK                 -- the name of the PK constraint
    PRIMARY KEY (student_id, parent_id),

  CONSTRAINT Student_Guardian_FK         -- the name of the FK constraint
    FOREIGN KEY (student_id) 
    REFERENCES Student (student_id)
      ON UPDATE CASCADE                  -- the actions of the FK
      ON DELETE RESTRICT,
  CONSTRAINT Parent_Guardian_FK          -- second FK
    FOREIGN KEY (parent_id) 
    REFERENCES Parent (parent_id)
      ON UPDATE CASCADE
      ON DELETE RESTRICT
) ;