I'm interested to know in which case I should create a primary key on multiple columns and why ?
To be more specific about my question, I want to know what are the advantages and disadvantages of primary key on multiple columns in terms of performance.
I did some research and found this question where someone is saying
Another good question is SHOULD a primary key be multiple columns. :) – Sonny Boy Nov 17 '09 at 17:31
Well, that's a different question, and the answer is NO, NOT EVER, AND IF YOU DO I WILL HUNT YOU DOWN AND HURT YOU. Just a personal preference, of course :-) – Stu Dec 4 '09 at 16:46
Despite the joke, there should be some good reason on why one shouldn't use primary key on multiple columns but I couldn't find any answer.
I was wondering about this because I have a concrete problem.
Consider the following table:
CREATE TABLE IF NOT EXISTS `user_travel_data` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_user` int(10) unsigned NOT NULL,
`day` date NOT NULL,
`origin` varchar(8) NOT NULL,
`destination` varchar(8) NOT NULL,
`data` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `ukey` (`id_user`, `day`, `origin`, `destination`)
)
In this table, for each user I have some data for each day and for each travel from origin to destination.
I need this table because it's very easy to use WHERE clause on it.
However, the numbers of line in this table are just insane. Usually, a user has ~50 origins and ~100 destinations.
With 30 days filled & 200 users, there's ~30 million lines in my table.
Because each user has around ~150000 lines, I had to create some tools for them to edit/delete/add lines in group.
And, because of these tools, users tends to delete old lines and create a whole bunch of new lines. This leads to a very fast increase of my primary key.
While it is not a problem now, it could be one pretty soon.
So, since I don't use the id on this table, I was wondering if I could do something like :
PRIMARY KEY (`id_user`, `day`, `origin`, `destination`)
This answer is also quite useful but didn't help with my problem
