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

Question

Imagine there are three tables in a MySQL database:

• posts
• Category
• category_post

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

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

• id (primary key, auto-increment, large integer)
• category_id
• post_id

Suppose we have 1,000,000 rows in our category_post table.

My question is:

Is there a performance advantage for the id column in the category_post table, or is it just taking up extra space?

Answer 1

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

A many-to-many relationship table is best done, for example,

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; 

In this case, you automatically get a "cluster" search in both directions and avoid unnecessary artificial identifier for the table.

(By the way, NB, implicit PK - 6 bytes, not 8. The topic has a long post by Jeremy Cole.)

One-to-many relationships do not need an extra table. Instead, one identifier is inside another table. For example, the City table will have an identifier for the country in it.

Answer 2

Having category_id and post_id as a composite primary key will have better performance than having an additional identifier as a primary key. This is because creating a primary key will also automatically create index . If you really need an extra ID column, you can improve performance by manually specifying the index in the categories_and_and_and_by_id. There is no benefit in having an additional key column, although this is usually bad practice.

Answer 3

doesn't have an id, but when you need to sort by pivot table, you will need to have an id or timestamp in the pivot table