Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have tables where the primary key is something like this:

-StoreID
-BusinessDate

Typically, there is one row for every store (~100) for every single day. One row for each store is inserted on a daily basis and is never updated and almost never deleted.

I think is would be better if the field order would be:

-BusinessDate
-StoreID

My reasoning is that the sort order of this key would more naturally follow the typical insert order. I'm wondering if I should change the order of the fields in the key.

So far performance has been acceptable. Will the current field ordering cause serious performance problems in the future if I don't change anything?

share|improve this question

1 Answer 1

The order of columns in a primary key does not affect the insert performance, since the combination of the values in the two columns of the primary key are meant to be unique.

However the order of index impacts the performance of SELECT queries. In the new order you will have a greater and increasing range of values (dates) across which the index is clustered (dates) as compared to clustering around 100 stores.

If most of your queries are focused on a single store with the new ordering your queries will be slower.

share|improve this answer
    
I think overall you've got this right, but the first statement seems off. Both values are not unique, but the combination of the values is and yes, that does affect the order in which the inserts are done because it must sort them into the appropriate place within the index (clustered or not). –  Grant Fritchey Mar 6 '12 at 11:43
    
@GrantFritchey thanks for pointing that out, I have updated my statement to reflect that –  ssmusoke Mar 6 '12 at 11:49
    
I still think your first statement is off. The order will affect how and where the data are sorted. It probably won't affect insert performance, though. –  Dave Markle Mar 6 '12 at 12:56
    
Well... it could affect insert performance too. If the structure you have encourages page splits, you're likely to see poorer performance. So, for example, the structure with StoreID as the leading edge is more likely to suffer from page splits than the one with the Date on the leading edge. So performance could suffer because of that, maybe. –  Grant Fritchey Mar 6 '12 at 13:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.