`primary`

and `secondary`

indices are orthogonal to `b-tree`

and `hash`

indices, and both of these are orthogonal to `clustered`

and `non-clustered`

indices.

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`

cannot. `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. `b-tree`

and `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 `clustered`

index.

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.