Alex Rivera | Logout

Composite Primary Key performance drawback in MySQL

Asked 2009-09-22T14:34:59.600
52

We have a table with a composite Primary key consisting of three fields (and it is in MySQL 5.1). There are near 200 inserts and 200 selects per second on this table, and the size of the table is around 1 million rows and it is increasing.

My question is: does the "Composite Primary Key" decrease the performance of the Inserts and Selects on this table?

Should I be using a simple Auto-Increasing INT ID field instead of a Composite Primary Key? (I think the answer is very much related to the way MySQL handles the Indexes on multiple columns)

mysql performance primary-key composite-key
Edit
Report

1 Answer

4
  1. Having that composite primary key slows down SELECTs a tiny bit, though the effect is pretty much negligible and not worth worrying about.
  2. Having those columns indexed at all slows down your INSERTs, and you certainly are doing enough INSERTs to worry about it. This is much more of a concern if it's a MyISAM table, where an INSERT locks the table, than if it's an InnoDB table. If, by going with the auto_increment primary key, you would be able to leave those columns unindexed, you would benefit from the change. If you would still need to keep those three columns indexed, though (for example, if you need to enforce uniqueness on the combination of them), it isn't going to do anything for you performance-wise.
answered 2009-09-22T14:49:36.810

Your Answer