Covering Index or individual indexes

Question

We have a Transactional Media table that contains a clustered primary key of 4 columns. RevenueCentreID, DateOfSale, MediaItemID and SaleTypeID. It also contains a quantity column and amount column.

Most our queries will be selecting Revenue Centres for a date range for a certain media item. We hardly ever use the SaleTypeID.

Would it be better to create an index on RevenueCentreID, DateOfSale, MediaItemID or a covering index on those three columns?

The RevenueCentreID and RevenueCentreID are foreign keys to a RevenueCentres Table and MediaItems table respectively. They will almost always be joined to when running queries.

A typical query will look something like the one below:

Select 
    RevenueCentreID,
    MediaItemID,
    DateOfSale,
    SUM(Quantity)Quantity,
    SUM(Amount)Amount 
From 
    Media m 
    Inner Join RevenueCentres rc on m.RevenueCentreID = rc.RevenueCentreID
    Inner Join MediaItems mi on m.MediaItemID = mi.MediaItemID
    Inner Join MediaCategories mc on mi.MediaCategoryID = mc.MediaCategoryID
Where
    rc.ProfitCentreID = 1
    And mc.MediaCategoryID = 1
    And (DateOfSale Between '20090713' and '20090719')
Group By
    DateOfSale,
    RevenueCentreID,
    MediaItemID

Any suggestions would be appreciated…

Answer 1

A good rule of thumb is to index every column you perform joins on, and every column you perform a query on.

Answer 2

Assuming SQL Server (you didn't specify exactly what system you're using):

If you specify these three item (RevenueCentreID, DateOfSale, MediaItemID) for almost every query anyway, then a single index on those three (if you always specify all three, or the same two of three) would be better than having three single indices.

The order must be so that the selection of the fields is all three, or the two to the left, or the single one to the left:

(RevenueCentreID, DateOfSale, MediaItemID)
(RevenueCentreID, DateOfSale)
(RevenueCentreID)

If you create the index in this order (RevenueCentreID, DateOfSale, MediaItemID), it won't be used should you frequently query on (RevenueCentreID, MediaItemID) (you can't have a column in the middle missing when you specify a WHERE clause).

Maintaining a single index that will be used most of the time is better than having three separate indices - it's just more overhead.

Marc