Keys/indexes for revisions of user submitted text

Question

I have a table:

id:int
revision:int
text:ntext

In general I will need to retrieve the latest revision of text for a particular id, or (considerably less frequently) add a new row containing a new revision for a particular id. Bearing this in mind, it would be a good idea to put indexes on the id and revision columns. I don't have a problem with implementing this, but I'm wondering if this is a situation where it would be sensible to use a composite (multi-field) index/key composed of both id and revision, or if there is any other strategy that would be appropriate for my use case?

Answer 1

It seems it the majority of cases you will be selecting the record based on both id and revision - therefore for quickest lookups you should make id and revision your composite primary key.

Answer 2

If id is the primary key its already indexed (I don't use SqlServer) I seems that your revision is unique too. so I think it would be better to use separate indexes and put unique constraint on revision (if required).

Answer 3

I don't think the performance difference between a composite index and two separate indexes would be noticeable, but, as usual, I suggest trying both and profiling if the absolute best performance is needed.

You are likely to always be querying on both fields, with a definite id and an unknown revision occasionally (when needing to find the max revision for an id). If your composite index is (id,revision) then this use case is supported by the index. Querying on id alone with no care for revision also works.

If it is ever likely that you will be querying on revision only without regard to id then you will need two separate indexes.

You will also want to analyze the impact that either index has on insert performance. The composite index will cluster on both fields, whereas the two separate indexes will cluster only on id.

EDIT: typos.