SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    Oct 2008
    Posts
    295
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question about many-to-many relations structure/indexes

    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    which of those three do i prefer? none of them




    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Oct 2008
    Posts
    295
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why dont you share more about the missing index?

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you should create two indexes (or should that be indices), on both columns in your example #2.

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by IBazz View Post
    you should create two indexes
    Quote Originally Posted by r937 View Post
    it's missing an index
    Two?
    Or one?

    And why?


  6. #6
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by TeNDoLLA View Post
    Why dont you share more about the missing index?
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •