SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot swiftone's Avatar
    Join Date
    Mar 2005
    Location
    Sunny Florida
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question MySQL optimization with indexes

    Can anyone help me understand the benefits and drawbacks to having multiple fields/columns of a table added to the Primary index?
    Also, perhaps someone can shed some light on how such a primary index is handled by MySQL.
    For instance, if I set up a Primary index on a table that contains 6 fields/columns that had all 6 fields/columns in the primary index. Would this give any benefit at all?
    The reason for the questions are because I am walking into a situation where such a case exists. I don't know that there is any benefit to doing Primary indexes this way, but can someone shed light on the why's?
    Happy Coding,
    Swiftone

    Unique Web Sites

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by swiftone
    For instance, if I set up a Primary index on a table that contains 6 fields/columns that had all 6 fields/columns in the primary index. Would this give any benefit at all?
    yes, a very clear benefit -- two, actually

    first, it means that the database will automatically prevent you from entering duplicate values for all 6 columns, i.e. all 6 values are considered when deciding if it's a duplicate set of values

    this is known as entity integrity

    secondly, a primary key allows you to reference it from a foreign key, which means that in a child table, you can be assured that the foresign key value (again, the combination of all 6 values) must actually exist in the primary key

    this is known as relational integrity
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot swiftone's Avatar
    Join Date
    Mar 2005
    Location
    Sunny Florida
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for the quick reply.

    Ok, I see some clear benefits to doing this in terms of ensuring uniqueness. However, how does this translate into speed? Will having such a large primary index hurt significantly on inserting? And, will doing the index in this way truly help SELECT statements over setting up a primary index and many secondary indexes?
    Happy Coding,
    Swiftone

    Unique Web Sites

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    integrity has nothing to do with speed and vice versa

    no, having a 6-column primary key does not significantly hurt inserting -- because if, for example, you used an auto_increment instead, you would still want to declare a unique key on the 6 columns as well (otherwise you're opening yourself up to duplicates, and "how do i remove all but one of my duplicate rows" has gotta be one of the most common pleas for help from people just using auto_increments)

    whether the primary key is of any use in optimizing SELECTs depends on what the SELECTs are asking for
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot swiftone's Avatar
    Join Date
    Mar 2005
    Location
    Sunny Florida
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks again r937.

    I have one more question that perhaps you can be of help with. I see one case of a primary index having 3 columns in it, then two of the columns are also set up as their own secondary indexes. Is there any benefit to this, or should I think of cleaning this up in some way?
    Happy Coding,
    Swiftone

    Unique Web Sites

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the secondary indexes would be very useful for queries which search based on those columns and not on the column which is the leftmost of the composite index

    for example, suppose you have a table of user preferences

    the primary key might be {user,preference}

    most time, you will search the table for the preferences of a specific user, so given the user value, the search would use the primary index, and be really efficient

    if you also occasionally search on preference (e.g. how many users prefer asparagus), then the primary index is not used, and you'd get a table scan unless you had a secondary index on preference
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot swiftone's Avatar
    Join Date
    Mar 2005
    Location
    Sunny Florida
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, as I was a bit confused on these items. It just seemed to me that if there was a Primary index of 6 columns, then the SELECT would scan through the full index of 6 columns when it may only need to scan one of those columns.

    Would you recommend creating secondary indexes in this case, or just sticking with the Primary? (Barring the knowledge of the frequency of this type of query.)
    Happy Coding,
    Swiftone

    Unique Web Sites

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by swiftone
    Would you recommend creating secondary indexes in this case, or just sticking with the Primary? (Barring the knowledge of the frequency of this type of query.)
    i would only create secondary indexes knowing which queries are required

    and, by the way, i never did know what the 6 columns in this case were
    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
  •