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
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
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”?