Does the order of keys in compound Primary key make any difference?

Question

Package

Package
-------
PackageID
PackageName
CityID
PRIMARY KEY (PackageID)
UNIQUE KEY (PackageName)
FOREIGN KEY (CityID)
  REFERENCES City (CityID)

Package versions:

PackageVersion
--------------
PackageID
VersionNumber
PackageType
PRIMARY KEY (PackageID, VersionNumber)
UNIQUE KEY (PackageID, VersionNumber, PackageType)
FOREIGN KEY (PackageID)
  REFERENCES Package (PackageID)
and subtypes (of versions):

LandPackageVersion
------------------
PackageID
VersionNumber
PackageType  DEFAULT 'L'
PRIMARY KEY (PackageID, VersionNumber, PackageType)
FOREIGN KEY (PackageID, VersionNumber, PackageType)
  REFERENCES PackageVersion (PackageID, VersionNumber, PackageType)

FlightPackageVersion
------------------
PackageID
VersionNumber
PackageType  DEFAULT 'F'
OriginID
PRIMARY KEY (PackageID, VersionNumber, PackageType)
FOREIGN KEY (PackageID, VersionNumber, PackageType)
  REFERENCES PackageVersion (PackageID, VersionNumber, PackageType)
FOREIGN KEY (OriginID)
  REFERENCES City (CityID)

When I define PackageVersion (read PackageVariant) table, I mark VersionNumber (aka idPackageVariant) as Auto Increment and a part of Primary Key in the order defined above:

PRIMARY KEY (idHolidayPackage, idPackageVariant)

CREATE  TABLE IF NOT EXISTS `wah_schema`.`HolidayPackageVariant` (
  `idHolidayPackage` INT UNSIGNED NOT NULL ,
  `idPackageVariant` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `packageType` VARCHAR(45) NOT NULL ,

  **PRIMARY KEY (`idHolidayPackage`, `idPackageVariant`) ,**

  UNIQUE INDEX `idHolidayPackage_UNIQUE` (`idHolidayPackage` ASC, `idPackageVariant` ASC, `packageType` ASC) ,

  INDEX `fk_HolidayPackageVariant_HolidayPackage` (`idHolidayPackage` ASC) ,

  CONSTRAINT `fk_HolidayPackageVariant_HolidayPackage`
    FOREIGN KEY (`idHolidayPackage` )
    REFERENCES `wah_schema`.`HolidayPackage` (`idHolidayPackage` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

However, due to this bug in mySql, I need to have AutoIncrement Key as the first part of the Primary Key in a compound key.

So, does the order of keys in compound Primary key make any difference ?

Just for reference, the above design comes from here.

score 3 · Accepted Answer · answered Mar 23 '12 at 15:15

The order of keys can make a difference depending on your access pattern.

Example...

You want to find MAX(idPackageVariant) for a singleidHolidayPackage

With idHolidayPackage as the second column, then the index will be scanned. With idHolidayPackage as the first, it can be seeked which is more efficient.

This is a specifiy MySQL problem of course. IIRCl you'll need to do this. InnoDB uses the first unique key as the clustered index which may or may not be the primary key. However, this may not be the case

CREATE  TABLE IF NOT EXISTS `wah_schema`.`HolidayPackageVariant` (
  `idHolidayPackage` INT UNSIGNED NOT NULL ,
  `idPackageVariant` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `packageType` VARCHAR(45) NOT NULL ,

  UNIQUE INDEX `idHolidayPackage_UNIQUE` (`idHolidayPackage` ASC, `idPackageVariant` ASC, `packageType` ASC) ,

  PRIMARY KEY (`idHolidayPackage`, `idPackageVariant`) ,

  -- not needed INDEX `fk_HolidayPackageVariant_HolidayPackage` (`idHolidayPackage` ASC) ,

  CONSTRAINT `fk_HolidayPackageVariant_HolidayPackage`
    FOREIGN KEY (`idHolidayPackage` )
    REFERENCES `wah_schema`.`HolidayPackage` (`idHolidayPackage` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

Alternatively, you can just have idPackageVariant as the primary and clustered key for efficiency.

Does the order of keys in compound Primary key make any difference?

1 Answers1