On Fri, 2010-10-29 at 10:19 -0700, Brian Ghidinelli wrote:
> Question about clustered indices. I have a table like:
> Clubs are 1-n to events in my system. In this particular table there
> are times when the event_id will be null.
If I understand correctly:
- an event is only associated with one club (event_id determines
- the combination of event and type is unique, but event is not
unique by itself (is this true?)
This means that your table is not normalized, and I recommend that you
consider a design like:
club_event ( club_id, event_id, ..., unique (event_id) );
event_type ( event_id, type, ..., unique(event_id, type) );
> I'll be running two queries:
> WHERE club_id = 'a' AND type = y
> WHERE event_id = 'b' AND type = z
> If I understand the value of clustering correctly, it would be best to
> have a clustered index on (club_id, event_id).
Perhaps, and you'd also want an (unclustered) index on event_id because
the second query doesn't query on the club_id.
If you choose my design you'd probably want to use a clustered index on
(event_type.event_id, event_type.type), and an unclustered index on
Of course, there are quite a few factors. When choosing a table design
and indexing scheme, you really need to be pretty specific about the
constraints, or else it's difficult to make good choices.
In response to
sfpug by date
|Next:||From: Josh Berkus||Date: 2010-10-29 20:36:45|
|Subject: Re: Clustered index when not always in the WHERE clause|
|Previous:||From: Brian Ghidinelli||Date: 2010-10-29 17:19:18|
|Subject: Clustered index when not always in the WHERE clause|