database index: why pairing

Question

I have a table with multiple indexes, several of which duplicate the same columns:

Index 1 columns: X, B, C, D
Index 2 columns: Y, B, C, D
Index 3 columns: Z, B, C, D

I'm not very knowledgeable on indexing in practice, so I'm wondering if somebody can explain why X, Y and Z were paired with these same columns. B is an effective date. C is a semi-unique key ID for this table for a specific effective date B. D is a sequence that identifies the priority of this record for the identifier C.

Why not just create 6 indexes, one for each X, Y, Z, B, C, D?

I want to add an index to another column T, but in some contexts I'll only be querying on T alone while in others I will also be specifying the B, C and D columns... so should I create just one index like above or should I create one for T and one for (T, B, C, D)?

I've not had as much luck as expected when googling for comprehensive coverage of indexing. Any resources where I can get a through explanation and lots of examples of B-tree indexing?

Answer 1

The rule with indexing is that an index can be used to filter on any list of columns that constitute a prefix of the columns used for that index.

In other words, we can use Index 1 when we filter on X and B, or X, B and C, or just X, or all four.

However, we cannot use the index to filter "in the middle". This is because indexes work not entirely unlike concatenating the values of those columns for each row, and sorting the result. If we know what the thing we're looking for begins with, we can figure out where in the index to look - just like when doing binary search.

That's why a single index is no good: if we need to filter on B, C, D, and one of X, Y and Z, we need three indexes; X, Y is no good as an index for just filtering on Y, because the prefix of the values we're looking for - the X - is not known.

As Daniel mentioned, a covering index is a possible explanation for repeating B, C, and D: even if D is never filtered on, it may be the case that we need exactly the columns which you see in your indexes, and we can then just read the columns from the index instead of just using the index to locate the row.