SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Jul 2008
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Which processes faster; single or mulit-column indexes?

    I tried searching through the forums for this but nothing seemed to cover this exactly.

    Given a vary simple database:
    Code MySQL:
    CREATE TABLE IF NOT EXISTS `Friend` (
      `user_id` int unsigned not NULL,
      `friend_id` int unsigned not NULL,
      `comment` varchar(250) default NULL)

    Which would make more sense:
    -- index (user_id) AND index (friend_id)
    or
    -- index (user_id, friend_id) AND index (friend_id, user_id)

    In theory, each combination can happen only once in the database (with numbers reversed as a separate unique).

    So there can be:
    1,2,comment
    and
    2,1,comment

    There are queries that look up a user_id and return all friends, and also looking up the friend and returning all the users that have "friended" them.

    I have been using just single-column indexes, but was curious if going to multi-column would help anything.

    Thanks for opinions and experiences.

    /Cyberfunkr

  2. #2
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,597
    Mentioned
    24 Post(s)
    Tagged
    1 Thread(s)
    If each of the two fields doesn't allow duplicates then specifying the second field in the index is redundant and provided that the database engine knows that there can be no duplicates the two versions should be treated identically. Of course since you also know it why not make sure of it and use the first version in the database so that it doesn't have to work out that you meant that when you entered the second version.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  3. #3
    SitePoint Member
    Join Date
    Jul 2008
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    If each of the two fields doesn't allow duplicates....
    Well, there can be duplicates within each column. However no combination can be duplicated, but users can have multiple friends. So a fuller example would be:

    1,2,comment
    2,1,comment
    1,3,comment
    1,4,comment
    2,4,comment
    4,3,comment

    etc.

    There cannot be a
    1,2,comment2

    Does that clarify things?

    Thanks


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
  •