I am trying to understand how covering indexes works. As far as I can see, if the query can be resolved by using only 1 index, it will use covering indexes, but as soon as there are multiple indexes involved, even if one of them do act as a covering index, it looks like we are loosing the covering index and we are back to using fetches…
For example, If I have documents with 5 fields, A, B, C, D, E…
I have the following indexes:
index1: index on fields A, B, C
index2: index on field D
index3: index on field E
For a query like:
SELECT A FROM bucket WHERE B = “aValue” ORDER BY C -> this uses index1 as a covering index, which is great
But then, sometimes I have extra filters in the where clause for fields D and/or E
So another query like:
SELECT A FROM bucket WHERE B = “aValue” AND C = “anotherValue” ORDER BY C -> this uses index1 and index 2 but do not show any usage of covering index and therefore do fetches! It should be able still to use index1 as a covering index.
Am I missing something?
I could multiply the number of indexes but this could get out of hand quickly if you have a lot of optional fields that can be mix and matched!