Skipping fields when querying collections with compound indexes

Suppose that a compound index is created lik so:

db.col_name.createIndex({A: 1, B: 1, C: 1})

Would this query work:

db.col_name.find({A: "some_value", C: "some_other_value"})

This query skips the use of the B field.

Hello @Big_Cat_Public_Safety_Act,

Yes, your query will use an index,
You need to read the exact example and explanation of your use case here:

Hi @Big_Cat_Public_Safety_Act,

The best way to confirm index usage and efficiency would be to explain() your queries. I recommend using MongoDB Compass’ visual Explain Plan feature to View Query Performance, but you can also run explain queries via the MongoDB shell or using your favourite MongoDB client/driver.

The compound index you created is a candidate to support your query since the prefix of the index matches your query (per the reference from @turivishal), but this won’t be efficient since all values of B will need to be scanned in order to match both A and C in your query.

An index on {A: 1, B: 1, C: 1} would be ideal for your query. An index on {A: 1, C: 1, B:1} would be better if B is skipped sometimes but is still commonly used.

For more details on compound indexes please see:


1 Like