mysqlperformance

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:

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:

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?


Solution

  • 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.