does a compound index in MongoDB improve multified match (not sorting)?

Question

The documentation seems a bit unclear on that.

1)It clearly states that compound indexes DO improve performance of multi-field SORTING (order and direction dependant).

2) There is one phrase which makes me think it will also improve multi field MACH (sql analogy: where a=1 and b=2 and c<5)

https://docs.mongodb.org/v3.0/tutorial/optimize-query-performance-with-indexes-and-projections/

If a query searches multiple fields, create a compound index.

it says nothing about sorting.

So is compound index on fields a,b,c better for matching performance than three single-field indexes for queries like (where a=1 and b=2 and c<5)?

okay i did some explains() and found out that it DOES prefer compond index in a sortless query than single field ones — Sergey Grechin
Normally speaking the more it can match before consulting the actual collection the better. There are exceptions to hat of course, but they normally apply cross database, i.e. in SQL and MongoDB equally. — Sammaye

Rudra Rudra · Accepted Answer · 2015-08-16T11:47:01

Yes, creating compound indexes improve query performance.

Create a Single-Key Index if All Queries Use the Same, Single Key

If for a particular collection you are using a single key for matching like where a=1

Create Compound Indexes to Support Several Different Queries

If your query uses one or more than one key, it better to use compound indexes.

db.sample.createIndex( { "a": 1, "b": 1, "c":1 } )

You can query on just a, you can query on a combined with b and also you can query on a, b and c. You can use explain() method to get to know what plan your query is using.

Index Intersection might also optimize the performance of your queries. It can support what compound index fails to support.

does a compound index in MongoDB improve multified match (not sorting)?

1 Answers