Re: Is Using Multiple Composite Primary key a Good Design Practice?
Posted by: Rick James
Date: August 07, 2015 10:52AM

I checked a system --
572 PRIMARY KEYs,
23% of them were composite
Longest had 6 columns

Performance and "best practice" dictate that the "optimal" PK be provided for the table. OK, that's vague. Let's discuss some potential issues:

Note 1:

PRIMARY KEY(a, b, c)
INDEX(d)

In InnoDB, the PK is implicitly included in each secondary index. So that is really like
INDEX(d, a, b, c)
making the index bulky. If (a,b,c) are TINYINTs, no big deal, If they are VARCHAR(255) utf8, then that is quite bulky.

If there are no secondary keys, this Note is irrelevant.

Note 2:

If one component of the PK (or other UNIQUE key) is a DATETIME or TIMESTAMP, then I question whether it is really unique -- can't you possibly get two items with the same second?

Note 3:

PRIMARY KEY(country, state, city)

Perhaps those should be normalized into another table, and you have only
PRIMARY KEY(location_id) ? Variants on this may be the 'right' way to deal with your 7-8 column PKs.

Note 4:

A FOREIGN KEY creates an INDEX to support the 'constraint'. Many operations require a lookup to validate the FK link; this is a performance hit. Purists like FKs; I prefer to validate my code so I don't need FKs. (OK, this has nothing to do with the number of columns in the FK.)

---
If you would care to show a few of the SHOW CREATE TABLE, I might come up with more comments on whether long PKs are OK or not OK.

Options: ReplyQuote


Subject
Written By
Posted
Re: Is Using Multiple Composite Primary key a Good Design Practice?
August 07, 2015 10:52AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.