Tell me more ×
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.
up vote 2 down vote favorite

I have a table with two columns, A and B. Each pairing (a, b) should only occur once in the table. When I query this table, I will only be interested in those rows where A has a certain value. Is a two column primary key (A,B) the way to go even though my queries will only search on A?

share|improve this question
1  
You know that performant is a noun, right? Why not use a more standard word that everyone understands, like efficient (or even the vague "best")? – Aaron Bertrand Jun 14 '12 at 23:50
en.wiktionary.org/wiki/performant – Dejas Jun 15 '12 at 0:07
So Wikipedia is an English teacher now? My comment was merely that jargon doesn't make it a word especially when there are alternatives that are more universal and less annoying. It smacks of people using "utilize" or "myriad" when "use" or "many" are much more natural and far less pretentious. – Aaron Bertrand Jun 15 '12 at 0:14
@AaronBertrand: "Best" is worst. This is a technical site; technical jargon is not only acceptable but to be expected and doesn't indicate pretension. "Performant" is more precise than "efficient" as it refers only to short run-time, rather than using least space. "Quickest", I suppose, would be the closest lay term. – outis Jun 15 '12 at 17:47
@outis ok, but how do you know that in this case "performant" means quickest? Maybe it also means least impact on index maintenance during DML operations. No matter which word is used, there needs to be some context along with it. I'm sorry if you find performant acceptable; I don't. Here's an example where it's stated better without using that "word": stackoverflow.com/questions/11055703/… – Aaron Bertrand Jun 15 '12 at 17:50
show 4 more comments

migrated from stackoverflow.com Jun 18 '12 at 3:30

3 Answers

up vote 7 down vote accepted

If you define the primary key as (a,b), then the index will be used when handling queries using a OR a AND b, but not b alone.

If you define the primary key as (b,a), then the index would be used when handling b, b AND a, but not a alone.

share|improve this answer
up vote 3 down vote

The primary key is not there as a mechanism to improve performance (by adding an index) it is there to guarantee data consistency. Design it to do that. If, at the same time, the index that comes with a PK improves performance on some specific query or group of queries, or if by ordering the columns in the PK properly it can be made to do that, great, but performance considerations should not ever drive the selection of the primary key columns!
If the PK as properly designed does not satisfy the indexing needs of your system,, add another index (in addition to the PK) to do that.

share|improve this answer
up vote 1 down vote

No, it will not be the best performing way (for reads in general).

Although it will be used when querying only against a, less keys will fit per page in the index as compared to an index only on a and so be less efficient because the keys are longer.

This will get worse depending upon the ratio of the size of b to a, i.e. the proportion of the key used for b which is effectively wasted space.

However, if you are querying only for a single or very few specific values of a rather than a range, the effect may not be significant, since you wouldn't be going through many pages.

I would add the unique index on both to meet data integrity constraints for your problem domain but see if the performance is better for your overall load by adding an index on a alone (note that having to update two indexes will slow performance on writes).

share|improve this answer

Your Answer

 
discard

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