4

For website that I maintain (eCommerce) I now need to make a table that seems to require Composite Primary Key of 5 columns. I never used 5 column as primary key before, only 2 and rarely 3.

I wonder whether there are any drawbacks to it, especially with regards to performance because this particular table is expected to have millions of records.

I haven't chosen a storage mechanism yet.

I am really rather interested in performance. How would for example "INSERT ... ON DUPLICATE KEY UPDATE" query work performance-wise in comparison to single column key? Are there any other performance drawbacks regarding to MySQL operations over this table?

Improve this question
9
  • For mysql, you ought to say which storage engine for this question if you want performance related responses. Mysql depends on storage engine for statistics and index navigation capabilities. Storage engines are not equal in these areas. I worked on a storage engine last year, quite revealing. Also, might help to say if the key column values change during the life of a row, and how big they are, data types. This would give people more insight to answer.
    – joshp
    Jun 3, 2015 at 7:59
  • a much better fit for this question is dba stackexchange site.
    – gbjbaanb
    Jun 3, 2015 at 8:08
  • 3
    Well for one thing, matching any single record necessarily requires that you provide all 5 columns, which is verbose and it also far more difficult to match than a unique number identifier. Most people just use unique number primary key and unique index constraint.
    – Neil
    Jun 3, 2015 at 8:09
  • 1
    You're better off just using a surrogate key rather than introducing all this complexity.
    – Robert Harvey
    Jun 3, 2015 at 14:29
  • 2
    In the specific case mentioned (performance of INSERT ... ON DUPLICATE KEY) MySQL will always perform a lookup against ALL keys of a table - not just the PRIMARY KEY. So if you add a surrogate key in addition to a unique constraint/index on 5 columns then performance will likely be worse and the database will be more complex (more columns, more indexes, more lookups to handle the surrogate key processing when data is inserted/updated). I don't see any basis for saying a surrogate would be "better".
    – nvogel
    Jun 4, 2015 at 6:16

2 Answers 2

Reset to default
0

There should not be any significant performance impact

A clustered index is basically free - why not use it?

Insert check for unique is an index seek
It is very very fast for single or composite key

Even if there performance implication for me data integrity wins every time
If the data has a natural composite key then you should use a composite key

I assume you have FK on the those five columns
The FK table should be ID, Value
And the FK should reference the ID
So now the index seek is on ID
Create a view to expose the PK values

Fragmentation will effect performance of both clustered and non cluster indexes. Try and arrange the order of the key to reflect the most common order of insert. Try and insert in the order of the composite key.

Improve this answer
1

The biggest drawback is that you require 5 values to identify a row. If your application uses an O/RM (Object/Relation Mapping) layer, then you will have fits mapping these database rows to objects in a programming language. O/RM's are easiest to set up when every table has a single column primary key.

Once you get into web application frameworks, URLs in the browser often include some sort of identifier for data in the database and assume there is only one piece of data required to identify a resource, e.g. a single column primary key. This makes using modern web application frameworks more difficult (Ruby on Rails, Microsoft's ASP.NET MVC to name a few).

Programming aside, what if in the future you need to add a column that together with the other 5 should be unique? Now you are modifying your composite key. Just keep it simple. Use a single column primary key. If you need those 5 columns to be unique, consider adding a unique constraint or unique index to those columns instead.

The next drawback of composite keys in general, and especially composite keys requiring this many columns, is all of this data needs to be specified and copied to child tables in order to set up proper relationships between tables.

  • Business rule changes in the future might dictate that these values can be duplicated. How will you identify data then?
  • Values are duplicated between tables when you need foreign key references
  • O/RM's are harder to configure
  • Modern MVC web application frameworks are harder to configure routes to include the composite keys

Nothing but downsides to me.

The biggest headache I've run into with developers is they assume "uniqueness of data" equates "identifying a row in the database". This is rarely the case. I've found applications and databases to be much more maintainable and easy to build by defaulting to single column primary keys, and using composite keys as an exception to the rule, then enforcing data uniqueness by using unique constraints or indexes on those columns.

Improve this answer
1
  • Thanks. They are all needed for unique identifier. No combination of 4 will be sufficient. I see that basically recommend to add new row (say, auto increment) that would be PK and mark those 5 columns as unique, I see practical advantages you mention. But that does not help me much as I was asking rather about performance. For example "INSERT ... ON DUPLICATE KEY UPDATE" query, it does matter to it whether constrain is PK or UNIQUE. How would this query pefrom in comparison to single column index?
    – Lukáš Rutar
    Jun 3, 2015 at 14:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.