Factors that determine the formation of a composite primary key?

What factors should be taken into account when determining what columns form a composite primary key?

From what I understand, a composite primary key is for uniquely identifying a row. But does how it forms affect performance? For example, if a composite primary key is formed by columns that are looked up frequently during queries, does the formation of such a composite primary key help in performance?

And is it a good approach to make the “id” column (auto-increment) as part of a composite primary key?

I can’t speak for performance, there will be others with opinions on that…my gut instinct is ‘the more complex a key, the longer it will take the database server to index by it’, but how that would affect query performance, I can’t speak to.

an id column is an antithesis of a composite key. Its sole purpose is to be a uniquely identifying column, which would mean it would serve as an artificial prime key.

1 Like

yes

but note that if the primary key is (colA, colB, colC) and there are searches done with values supplied for colB only, or colB and colC only, then separate indexes will be required to make those searches perform well

no… if you have an auto_increment, you can make it the PK by itself, and add other indexes as required

1 Like

@m_hutley
Thanks. I agree that making “id” column as part of a composite primary key is not a good idea.

@r937
Thanks. So it is redundant to make “id” column as part of a composite primary key.

I was advised to make “id”, “category_id”, and “publish_date” columns of the “Article” table of a news website as a composite key, and the reason is that it is good for performance. Now it looks like that advice is not completely right.

According to your advice, I guess I should make “id” column be the primary key, and then add “category_id” and “publish_date” columns as indexes.

I’m still pretty new to database design. Could you kindly confirm that the following code is the correct code for me to use?
ALTER TABLE Article ADD INDEX(category_id, publish_date);

what happened when you tested it? ™

just kidding, the answer is yes

note, too, that if you have any queries that search on publish_date across all categories, you’ll want an additional index on that column alone

1 Like

Thank you for confirming. :slight_smile:

Sorry for not being clear. That ALTER TABLE ... code was actually copied from my phpMyAdmin result of manually clicking the “Index” option. I was just not sure if I did the correct action.

And thanks for the tip of creating an additional index. That helps. :slight_smile:

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.