If your table comes with an index
(A,B, C,D) would there be reason also to possess the index
(A,B, Y,Z) or
The main reason I am asking is the fact that these two offer an chance to conflate areas of the index, within the first situation the prefix
A,B and within the second situation the suffix
C,D, permitting complex indexes to become added cheaper. Nevertheless, I have never mind of the being carried out and I am wondering if there's little if any benefit to getting both individuals indexes it or maybe I have just skipped something.
You will find certainly cases when it seems sensible to possess both indexes
(A,B,Y,Z). Should you think that A &lifier B are often used posts that aren't terribly selective as the mixture of C &lifier D or Y &lifier Z tend to be more selective but less often used. Or in instances where A &lifier B are selective by themselves but it is helpful to possess a covering index for several queries which are also choosing either C &lifier D or Y &lifier Z. As well as cases when both
(A,B,Y,Z) should be unique. You can even develop cases when it might be useful to possess a third index on
(A,B) because you need to make index range scans for queries that simply specify A &lifier B as efficient as you possibly can. However these are usually unusual cases.
I am unsure which i follow your point concerning the chance to conflate indexes. I am unfamiliar with any database engine that will allow multiple indexes to make use of exactly the same index structure if that is what you are recommending.