secondary indices are orthogonal to
hash indices, and both of these are orthogonal to
The first class of indices are logical indices. They are high-level indicators about the structure of your data. The primary key should uniquely identify your data, which means it should be one of what relation database theory calls a "candidate key". Secondary indices are for speeding up queries against other fields than the main index and they do not need to be candidate keys.
The second class are specific index implementations, fit for slightly different queries.
b-tree can fulfill range queries (
WHERE c > 3 AND c < 7) which
hash is however O(1) (constant time) in the average case on exact queries (
WHERE c = 5), while
b-tree is O(lg n) (logarithmic time) always.
hash are both well-defined datastructures, but they have very little to do with your actual data.
The third class defines if the entire table is sorted on disk by the sorting order of the index. If that's the case it's called a
As you can see an index can be a
primary clustered b-tree or a
primary clustered hash or some other combination drawn from these three different classes.
If you were asked which indices to introduce, the answer would foremost be from the first class. The question is probably about the schema. That's your starting point at least. Only when you have decided what indices to introduce do you need to think about whether they should be b-trees, clustered etc. The first class is a logical one, tightly coupled with how you are using the data. The others are implementation details.