SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    $books++ == true matsko's Avatar
    Join Date
    Sep 2004
    Location
    Toronto
    Posts
    795
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    1 Index 2 Columns or 1 1/2 Index's 3 columns

    I am currently redesigning the database layout of my online community website. There is one table in particular which stores the users who are friends with the other users. The table currently has 3 columns.

    id | owner | user

    - the id is the KEY id per row (used for deletion)
    - the owner column is the owner_id of the buddy list
    - the user column is the user_id of a member on the buddy list

    there are 2 index's, the owner INDEX and the id KEY index.

    The reason why I call this a 1 1/2 system is beacause of the unique KEY id system. KEY index's are stored within the row, and not in INDEX file (for MyISAM tables and maybe some other ones).

    Anyways, I want to aviod using the id column because users continuosly add and remove users on their friends list. This causes the ID values to increase since it is an AUTO_INCREMENT, and it would be a drain on the database to look for the lowest free id each time.

    So the solution that I have come across is to remove the id column and to have an INDEX that goes across the owner and user column. The owner So a DELETE query would look like this

    DELETE FROM buddies WHERE owner='1' AND user='2' LIMIT 1

    This works fine, however I have the paranoia that since there are two INDEX type index's that it will cause a pileup in the index file.
    I can't believe I ate the whole thing

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    no, your design is fine, in fact it's better than the one with the auto_increment

    here's the way i would design it --

    create table buddies
    ( owner integer not null
    , user integer not null
    , primary key (owner,user)
    , index secondkey (user,owner)
    );

    make sense?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    $books++ == true matsko's Avatar
    Join Date
    Sep 2004
    Location
    Toronto
    Posts
    795
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah it does make sence, however I never knew that you could have a PRIMARY KEY across more then one column...

    wow this changes alot of things

    and... does that mean that it will still stay distinct?

    so if I have two records like this

    1,2
    1,2

    then it wouldn't work since the PRIMARY key column only allows for no duplicate rows, right?
    I can't believe I ate the whole thing

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    yeah, the composite primary key means that each owner can only add a given user at most once (it wouldn't make sense to have the same guy on your buddy list twice)

    and the secondary key is for when you're going to search "whose buddy lists is joe on"

    both the primary key and the secondary index are what's known as covering indexes, in other words, any query you run against this table won't actually access the table at all, since all the columns are available in the index
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    $books++ == true matsko's Avatar
    Join Date
    Sep 2004
    Location
    Toronto
    Posts
    795
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok so what you said about covering indexes, I understand exactly what you mean.

    However, since the 2nd index is across both columns then a query like so would work...

    SELECT * FROM buddies WHERE user='1' AND owner='2'

    I doubt you would want to search for a particular owner of a buddy list who is the owner of one person (only one result). However where it might come in handy is in a JOIN statement.

    You see I still dont understand if a particular index is needed when two tables are joined. For example.

    SELECT username FROM buddies b INNER JOIN users u ON b.owner=u.user_id WHERE b.user='1' ORDER BY username

    ok, now at the part where the tables join (the owner id), would the owner_id on the buddies table need to have an index (since all the rows are being selected). I know you have to have one on the users table.

    so is the index needed then?
    I can't believe I ate the whole thing

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by matsko
    ok, now at the part where the tables join (the owner id), would the owner_id on the buddies table need to have an index
    yes, but we already declared that index -- it's the primary key

    the optimizer will gladly use the composite index if the column it is looking for is the leftmost one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    oh, wait, you have WHERE b.user_id=1, so you actually are searching on the owner of a buddy, in which case it will use the secondary index we declared to do the search
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    $books++ == true matsko's Avatar
    Join Date
    Sep 2004
    Location
    Toronto
    Posts
    795
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok we know that it will use the user_id part (on the WHERE clause)

    but will it also use the owner_id part (for the INNER JOIN)? the owner_id is a shared index with the user_id column, so if it does NOT use the owner_id for the INNER JOIN matching then I wont use it...
    I can't believe I ate the whole thing

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    it definitely will use it

    okay, let's say you wanted to find out fred's phone number

    you look in the white pages, which is conveniently arranged by name, and badaboom, you got it

    okay, now let's say you want to find out who has phone number 123-4567

    you need an index of phone numbers and names but this index has to be in phone number sequence

    when you look up the phone number, what do you do with the name? that's right, you use it

    same with your buddy index -- the compound secondary key on (user,owner) will let you look up a particular user, and get the owner from the same index entry

    if it weren't a compound index, you'd have to go back to the table (using the internal row pointers from the index to the rows) to find out who the owner is

    if the index on phone numbers that's in phone number sequence didn't have a name beside 123-4567, but instead some other internal value, like DW53FR57EO85C that you then had to look up elsewhere to find out which name that corresponds to, what good would that be?
    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
  •