Primary key and indexes

[hanzoff]

Hi,

Lets say I have registrations table where
primary key (event_id, user_id)
and I want to query by event_id and user_id together and separately, what kind of additional indexes should I add? If I just add index to user_id then what happens if I query only by event_id? Will it use primary key index? Would it be considerably faster if I also added separate index to event_id?

Thanks!

[dagon]

if you query either and both, then you would have 3 indexes (indices) one for each and one for both.

[CoderDan]

you can have multiple indexes on a table, and a primary key is an index by default.
If you plan to query by one field or the other, both should be indexes. if you plan to query for a combination of both, you can create an index that contains both fields - this is rare however, most cases you are only using one field as an index at a time