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
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)
is there any drawback using long Composite primary key instead of indexes? (some table use 5 columns(integer) as primary key)
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)
should i use a surrogate key(auto_increment) in first column
and use indexes or unique instead?