问题描述:

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…

网友答案:

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

网友答案:

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

相关阅读:
Top