CODEWITHSUNDEEP
mongodbindexing
stackdisplay
stackdisplay

Reputation: 2045

Multiple single field vs compound index

what is the difference of create multiple single field index vs a compound index as follow

db.products.createIndex( { "item": 1 } )
db.products.createIndex( { "stock": -1 } )

vs

db.products.createIndex( { "item": 1, "stock": -1 }

Also, after i create a compound index do i need to separately create a single field index for each item, stock? if yes, in what scenario do i need to do that?

Upvotes: 3

Views: 1840

Answers (1)

dilsingi
dilsingi

Reputation: 2958

Answer depends on your query pattern.

The compound index satisfies the following queries

  • Find on "item"
  • Find on "item" and "stock"
  • Find on "item" and order by "stock"
  • Find on "item" and "stock", order by "item asc", "stock desc"

The above compound index will not be used for query on "stock" field alone. So you need a separate index for that.

The single index satisfies query with that particular field alone.

  • For example index on "item" will satisfy queries with "item" in the find criteria.
  • Similarly index on "stock" will satisfy queries with "stock" in the find criteria.

If these two indexes exist separately and the query contains both "item" and "stock", then index being picked depends on higher cardinality. That is which field amongst the two, will allow MongoDB to narrow down on the result set better.

Further reading for compound index.

Upvotes: 5

Related Questions