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

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?

Posts and categories are 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.