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

mysql, performance

Solution

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.

Problem

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?

Original source