Question about many-to-many relations structure/indexes

Databases
1

Which one of these three techniques you would prefer and why for many-to-many situations (using MySQL)?

  1. Three fields in the link table, id, linked_id1, linked_id2. (InnoDB)
    id = primary_key
    linked_id1 (linked to the host table with foreign_key)
    linked_id2 (linked to the host table with foreign_key)

  2. Two fields in the link table, linked_id1 and linked_id2. (InnoDB)
    primary_key = (linked1, linked2)
    linked_id1 (linked to the host table with foreign_key)
    linked_id2 (linked to the host table with foreign_key)

  3. Two fields in the link table, linked_id1 and linked_id2. (MyISAM)
    primary key = (linked_id1, linked_id2)
    unique index = (linked_id2, linked_id1)
    And then join data in code.

Or something totally different and why ?

2

which of those three do i prefer? none of them

:smiley:

i would use foreign keys, so that rules out 3

i would never use an auto_increment id in a relationship table, so that rules out 1

2 is very close to ideal, but it’s missing an index

3

Why dont you share more about the missing index? :slight_smile:

4

you should create two indexes (or should that be indices), on both columns in your example #2.

5

Two?
Or one?

And why?

:slight_smile:

6

so when joing to the table, it’ll work efficiently no matter which column the join was on. PK is one index and another reverse index should be created.

bazz

7

please see this post

the PK is an index, and then you create a second one on the same columns but in the other order (see the index highlighted in blue)