MongoDB does compound index on multiple fields indexes the _id column by default or not?

Question

I am not clear on whether the mongodb would use the index that I created or not. Below is how my data looks like

_id: an string
field1: string
field2: array
field3: boolean
field4: integer

Now I created an compound index as below

db.mycollection.createIndex({"field1":1, "field2": 1, "field3": 1, "field4": 1});

Now suppose I issue the below query, prefix search on id and equality on the rest of the field

db.mycollection.find({"_id":/^abc/, "field1": "pqr", "field2": "xyz", "field3": true, "field4": 123});

would the above query use the compound index that I created above or not? Do I have to include the _id as well during index creation as below?

db.mycollection.createIndex({"_id": 1, "field1":1, "field2": 1, "field3": 1, "field4": 1});

Any help would be really appreciated.

Use explain to find out the execution plan for your query given the defined indexes. Execution plan will say whether indexes are used and if so which ones. — D. SM

prasad_ prasad_ · Accepted Answer · 2020-07-01T04:00:18

Now suppose I issue the below query, prefix search on id and equality on the rest of the field db.mycollection.find({"_id":/^abc/, "field1": "pqr", "field2": "xyz", "field3": true, "field4": 123}); would the above query use the compound index that I created above or not?

There is a default unique index on the field _id and your index using fields field1+field2+field3+field4. The query optimizer can use any one of the two indexes. As I tried with similar data, index and query, I see that it used the default index on the _id field.

To verify if a query is using an index and which index is being used, use the explain method on the query. This generates a query plan which will show which index is used when the query gets executed.

Do I have to include the _id as well during index creation as below?

You can include the _id field as part of the compound index. For example, as you had posted the index on {"_id": 1, "field1":1, "field2": 1, "field3": 1, "field4": 1}. Note that after creating the index there will be two indexes - one on the _id field and the new compound index including the _id.

The query will use the compound index to perform the read. You can verify that as well by generating a query plan using the explain.

Note, the index on the _id filed cannot be deleted (and user created indexes can be). To list the indexes on a collection, use the command db.collection.getIndexes().

Note on using compound indexes:

One of the factors to consider when using compound indexes is query selectivity. Selectivity is the ability of a query to narrow results using the index... See Create Queries that Ensure Selectivity.
Creating index on more fields can be an overhead while inserting data into the collection (as the index also needs creation). Also, a large sized index needs more memory during operation.

MongoDB does compound index on multiple fields indexes the _id column by default or not?

2 Answers