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