patternsqlMinor
Order of composite key for best lookup performance
Viewed 0 times
orderforcompositeperformancelookupkeybest
Problem
I have a table with a composite key made up of two fields:
For optimal lookup performance, what should be the order of the fields in the primary index?
A and B.A is like a user ID or machine ID, it is globally unique. B has only a few possible values (less than 5). The composite key will look like (user1 + B1), (user2 + B1), (user3 + B1), (user1 + B2) etc. I don't want to split the data into a few tables by B since the number of rows has not reached that amount yet.For optimal lookup performance, what should be the order of the fields in the primary index?
AB or BA?Solution
For a query that is doing an equality seek on both columns it doesn't matter. You'd need to look at your other queries to see how they would benefit from either possibility.
An index on (A,B) could also serve for an equality seek on A but not an equality seek on B for example.
Also if you had ORDER BY A,B on a query then an index on A,B could avoid a sort.
You say you have no such queries and all are strict equality using both columns.
As a tiebreaker you might consider whichever leading column would make the most useful histogram but for the query pattern you describe where neither A or B are ever queried except together to look up a key in an unique index neither will be useful.
So in your case it doesn't matter.
BTW: There is a persistent belief that putting the most selective column first is required.
This doesn't make any difference here. Either way SQL Server will be able to seek on both columns. Contrary to the StackOverflow answer linked in the comments there is no big difference in efficiency either way (I guess there might be a minor benefit in CPU time by checking the column most likely to differ first and thus potentially avoiding the need to do a comparison for the second key column in the composite key but even if true this would be a micro optimisation)
Sometimes this will make sense for other queries in your workload though. E.g if there are queries against both A and B individually and the index wasn't covering for either of them but one of the columns was selective enough to make a plan with lookups worthwhile then putting the most selective first would make sense in that instance.
An index on (A,B) could also serve for an equality seek on A but not an equality seek on B for example.
Also if you had ORDER BY A,B on a query then an index on A,B could avoid a sort.
You say you have no such queries and all are strict equality using both columns.
As a tiebreaker you might consider whichever leading column would make the most useful histogram but for the query pattern you describe where neither A or B are ever queried except together to look up a key in an unique index neither will be useful.
So in your case it doesn't matter.
BTW: There is a persistent belief that putting the most selective column first is required.
This doesn't make any difference here. Either way SQL Server will be able to seek on both columns. Contrary to the StackOverflow answer linked in the comments there is no big difference in efficiency either way (I guess there might be a minor benefit in CPU time by checking the column most likely to differ first and thus potentially avoiding the need to do a comparison for the second key column in the composite key but even if true this would be a micro optimisation)
Sometimes this will make sense for other queries in your workload though. E.g if there are queries against both A and B individually and the index wasn't covering for either of them but one of the columns was selective enough to make a plan with lookups worthwhile then putting the most selective first would make sense in that instance.
Context
StackExchange Database Administrators Q#147383, answer score: 5
Revisions (0)
No revisions yet.