MySQL Many-to-many Mapping table

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Insert
> Step 2: And Like the video. BONUS: You can also share it!

Remarks

  • Lack of an AUTO_INCREMENT id for this table -- The PK given is the 'natural' PK; there is no good reason for a surrogate.
  • MEDIUMINT -- This is a reminder that all INTs should be made as small as is safe (smaller ⇒ faster). Of course the declaration here must match the definition in the table being linked to.
  • UNSIGNED -- Nearly all INTs may as well be declared non-negative
  • NOT NULL -- Well, that's true, isn't it?
  • InnoDB -- More effecient than MyISAM because of the way the PRIMARY KEY is clustered with the data in InnoDB.
  • INDEX(y_id, x_id) -- The PRIMARY KEY makes it efficient to go one direction; the makes the other direction efficient. No need to say UNIQUE; that would be extra effort on INSERTs.
  • In the secondary index, saying just INDEX(y_id) would work because it would implicit include x_id. But I would rather make it more obvious that I am hoping for a 'covering' index.

You may want to add more columns to the table; this is rare. The extra columns could provide information about the relationship that the table represents.

You may want to add FOREIGN KEY constraints.



Got any MySQL Question?