CODEWITHSUNDEEP
mongodb
Jonathan Clark
Jonathan Clark

Reputation: 20558

Add _id when ensuring index?

I am building a webapp using Codeigniter (PHP) and MongoDB. I am creating indexes and have one question.

If I am querying on three fields (_id, status, type) and want to create an index do I need to include _id when ensuring the index like this:

db.comments.ensureIndex({_id: 1, status : 1, type : 1});

or will this due?

db.comments.ensureIndex({status : 1, type : 1});

Upvotes: 3

Views: 2503

Answers (4)

Raghu
Raghu

Reputation: 28

_id is unique, and by default an index is created on _id field. Given _id is unique, you would never need to query on multiple fields if query includes "_id" as that result in single document.

From: MongoDB documentation

Default _id Index MongoDB creates a unique index on the _id field during the creation of a collection. The _id index prevents clients from inserting two documents with the same value for the _id field. You cannot drop this index on the _id field.

Upvotes: 0

JohnnyHK
JohnnyHK

Reputation: 312149

You would need to explicitly include _id in your ensureIndex call if you wanted to include it in your compound index. But because filtering by _id already provides selectivity of a single document that's very rarely the right thing to do. I think it would only make sense if your documents are very large and you're trying to use covered indexes.

Upvotes: 5

Stennie
Stennie

Reputation: 65433

MongoDB will currently only use one index per query with the exception of $or queries. If your common query will always be searching on those three fields (_id, status, type) then a compound index would be helpful.

From within the DB shell you can use the explain() command on your query to get information on the indexes used.

Upvotes: 4

pangabiMC
pangabiMC

Reputation: 784

You don't need to implicitly create index on the _id field, it's done automatically. See the mongo documentation:

The _id Index

For all collections except capped collections, an index is automatically created for the _id field. This index is special and cannot be deleted. The _id index enforces uniqueness for its keys (except for some situations with sharding).

Upvotes: -4

Related Questions