Is there any benefit to having an auto-incrementing primary key in a MySQL pivot table?

Question

Imagine we have three tables in a MySQL database:

• posts
• categories
• category_post

There is a one-to-many relationship between posts and categories so that a single post may have many categories.

The category_post table is the pivot table between categories and posts and has the following columns:

• id (primary key, auto-incrementing, big integer)
• category_id
• post_id

Let's also imagine that we have 1,000,000 rows in our category_post table.

My question is:

Is there any performance benefit to having the id column in the category_post table or does it just take up extra space?

Answer 1

Posts and categories is probably many-to-many, not one-to-many.

A many-to-many relationship table is best done something like

CREATE TABLE a_b (
a_id ... NOT NULL,
b_id ... NOT NULL,
PRIMARY KEY (a_id, b_id),
INDEX(b_id, a_id) -- include this if you need to go both directions
) ENGINE = InnoDB;

With that, you automatically get "clustered" lookups both directions, and you avoid the unnecessary artificial id for the table.

(By the way, N.B., an implicit PK is 6 bytes, not 8. There is a lengthy post by Jeremy Cole on the topic.)

A one-to-many relationship does not need this extra table. Instead, have one id inside the other table. For example, a City table will have the id for the Country in it.

Answer 2

Having category_id and post_id as a compound primary key will have better performance than having an extra id as a primary key. This is because making it a primary key will also create an index on it automatically. If you really want an extra Id column you can improve performance by manually defining an index on category_id and post_id. There is no benefit of having an extra key column though and this is generally a bad practice.

Answer 3

not having id is good, but when you care about ordering by the pivot table you will need to have id or timestamp in pivot table