Confusion with prefixes in Compound Indexes

I have a little confusion with prefixes in compound indexes, suppose there is a compound index {a:1,b:1,c:1,d:1} for a collection alphabet , and we run a query db.alphabet.find({"b":"xyz","a":"uvw"}).sort({"c":-1}) will this query use the indexes. If yes then why?

Can anyone help me with this.

Thanks and Regards

In the example that you gave, the filter will be reorganised to become {a: "uvw", b: "xyz"} which will enable two things:

  1. the use of the index prefix {a: 1, b: 1
  2. the use of c for sorting. Because a, b is followed immediately by c in the index.

If the sort was on d, the index will be used only on the filter and not on the sort.

I’d suggest going through the When you can sort with Indexes again.

1 Like

@007_jb

So, order of the keys in the query dosen’t matter for using the indexes instead if any key is missing from the prefixes order will only restricted the index scan. That’s what you mean?

Correct! The query optimiser will reorganise as it see fits. And when a prefix key is skipped/missing in between, it will stop to use the index from that point on.

@007_jb

Okay, thanks for the help.

Hi @iamaeytee,

I would like to add one more point to @007_jb’s reply:

If the query does not specify an equality condition on an index prefix that precedes or overlaps with the sort specification, the operation will not efficiently use the index.
Please refer to the following link for more details:

Please let me know, if you ave any questions.

Thanks,
Sonali

Hi @Sonali_Mamgain,

Okay, thanks for the help.

Regards

Thank you very much for your information. Your answer resolved the puzzle for me. However, how can I skip the filter optimization so that I can use explain() to approve the sort order really matter?

Hi @Qi_Chen,

Let’s try to understand the result of the explain command. You can refer to this documentation.

Can you elaborate a bit more on your question? If you can share your query and index and we can work together to understand the explain result.

Please feel free to reach out if you have any questions.

Kind Regards,
Sonali