Composite primary key , any drawback?

  • Filter
  • Time
  • Show
Clear All
new posts

  • Composite primary key , any drawback?

    Hi guys

    I m now working with kind of webboard (community website).
    and i ve some questions here.

    i ve a USER_COMMENT table (innodb)
    assume 20% insert/update/delete, 80% select

    USER_ID (int)
    POSTER_ID (int)
    POST_TIME (int)
    MESSAGE (text)
    and primary key (user_id, post_time, poster_id) - it's always unique by using these 3 columns
    example queries to be used
    (select from whrere user_id = 111 order by post_time)
    (delete whrere user_id = 111 and post_time = 111 and poster_id = 112)
    (select from whrere user_id = 111 and poster_id = 112 order by post_time)

    1. is there any drawback using long Composite primary key instead of indexes? (some table use 5 columns(integer) as primary key)

    2. if i add some additional indexes (secondary index) will it be slowdown by long composite primary key? (some articles here say -if you use long primary key, there will be some overhead on secondary index)

    3. should i use a surrogate key(auto_increment) in first column
    and use indexes or unique instead?

    Many thx