Is there ever use for DB indexes like this?

If a table has an index (A,B,开发者_运维技巧 C,D) would there ever be reason to also have the index (A,B, Y,Z) or (B,A, C,D)?

The reason I'm asking is that both of these provide an opportunity to conflate parts of the index, in the first case the prefix A,B and in the second case the suffix C,D, allowing complex indexes to be added at a lower cost. That said, I've never head of this being done and I'm wondering if there is little or no advantage to having both those indexes it or if I've just missed something.

There are certainly cases where it makes sense to have both indexes (A,B,C,D) and (A,B,Y,Z). If you assume that A & B are frequently used columns that are not terribly selective while the combination of C & D or Y & Z are more selective but less frequently used. Or in cases where A & B are selective on their own but it's useful to have a covering index for certain queries that are also selecting either C & D or Y & Z. Or even cases where both (A,B,C,D) and (A,B,Y,Z) must be unique. You could even come up with cases where it would be worthwhile to have a third index on (A,B) because you want to make index range scans for queries that just specify A & B as efficient as possible. But these tend to be unusual cases.

I'm not sure that I follow your point about the opportunity to conflate indexes. I'm not familiar with any database engine that would allow multiple indexes to use the same index structure if that's what you're suggesting.