CODEWITHSUNDEEP
mysqldatabaseprimary-key
Merijndk
Merijndk

Reputation: 1693

mysql auto increment id vs combined fields primary key

I'm having a diffecult time figuring out what to use for my primary key.

My table:

| gender | age   | value | date updated | page id(the forgein key) |
| M      | 15-24 | 100   | some date    | 1
| M      | 25-34 | 120   | some date    | 1
| M      | 35-44 | 110   | some date    | 1
| F      | 15-24 | 190   | some date    | 1
| F      | 25-34 | 230   | some date    | 1

Now I need to add a primary key. I could either add a id field with auto increment and make that the pk but that id will not be used as forgein key or anything else in another table so it would be kind of useless to add it.

I could also combine the page, gender and age and make them the primary key but I am not sure what the advantage on that would be. I tried googling for a while but still not sure what to do.

Upvotes: 0

Views: 854

Answers (2)

kiks73
kiks73

Reputation: 3758

I don't think that using an auto increment key, or using gender and age as a composite primary key would significantly change performance.

Anyway primary key on gender and age should be a nice choice as also it prevents duplicate entries (you can't repeat the same pair of values in other records) and leaves the table structure more clear.

Upvotes: 1

Noob
Noob

Reputation: 752

Please read the documentation of MySQL:

The primary key for a table represents the column or set of columns that you use in your most vital queries. It has an associated index, for fast query performance. Query performance benefits from the NOT NULL optimization, because it cannot include any NULL values. With the InnoDB storage engine, the table data is physically organized to do ultra-fast lookups and sorts based on the primary key column or columns.

If your table is big and important, but does not have an obvious column or set of columns to use as a primary key, you might create a separate column with auto-increment values to use as the primary key. These unique IDs can serve as pointers to corresponding rows in other tables when you join tables using foreign keys.

Thanks @AaronDigulla for his explanation...:

Necessary? No. Used behind the scenes? Well, it's saved to disk and kept in the row cache, etc. Removing will slightly increase your performance (use a watch with millisecond precision to notice).

But ... the next time someone needs to create references to this table, they will curse you. If they are brave, they will add a PK (and wait for a long time for the DB to create the column). If they are not brave or dumb, they will start creating references using the business key (i.e. the data columns) which will cause a maintenance nightmare.

Conclusion: Since the cost of having a PK (even if it's not used ATM) is so small, let it be.

From my experience and knowledge if you do not define your primary key the database will create an hidden primary key. So in your situation best solution is to create it anyway.

Upvotes: 2

Related Questions