Been looking into using multi-column primary keys and as performance is extremely important with the size of traffic and database I need to know if there is anything to consider before I start throwing out the unique ID method on many of my tables and start using mulit column primary keys.
So, what are the performance/optimisation pros/cons to using multi column primary keys versus a basic single column, auto-inc primary key?
Is there a particular reason that you need/want to use multi-column keys instead of an (I assume) already created single-column key?
One of the problems with Natural Keys is dealing with cascading an update to the key value across all the foreign keys. A surrogate key such as an auto-increment column avoids this.
In terms of performance, depending on the row count, the data types of the columns, your storage engine, and the amount of RAM you have dedicated to MySQL, multi-column keys can affect performance due to the sheer size of the index.
In my experience, it is almost always easier in terms of development and maintenance to use a surrogate key as the PK and then create indexes that cover your queries across the natural keys. However, the only way to determine the true performance impact for your application is to benchmark it with realistic a realistic load and dataset.
HTH -
Chris