Why have “unidirectional ‘mapping’” tables?

Question

I've been reading about how Pinterest sharded their MySQL databases here.

One of the things they talk about in that post is having “unidirectional ‘mapping’” tables such as

 CREATE TABLE board_has_pins (
  board_id INT,
  pin_id INT,
  sequence INT,
  INDEX(board_id, pin_id, sequence)
) ENGINE=InnoDB;

and then an opposite pin_owned_by_board.

My question is, why would you have these “unidirectional tables” for “mapping” and not just one with a different index structure? I don't think it can stop duplicates due to the sequence being a unix timestamp, but the index does limit only being able to select by the board_id in this instance or the full index (efficiently anyway). So why not have separate indices on board_id and pin_id? Is this down to the fact that a pin could in theory live on a separate shard even though they try to keep them on the same one so it makes sense to make it “unidirectional”?

Answer 1

I agree that it is a strange table.

No uniqueness constraint, so dups can occur. I would change INDEX to PRIMARY KEY.

No index the opposite direction, so unlikely to use it that way. I would add another column and INDEX(bin_id, board_id, pin_to_board sequence)

On the other hand, perhaps there is a lot of activity in the table -- such as updating all the sequence values frequently. Still, InnoDB would really prefer to have a PRIMARY KEY, and that triple seems like the 'right' PK.

I would probably work harder to avoid potential timestamp dups.