CODEWITHSUNDEEP
performancemongodbindexing
LukeSolar
LukeSolar

Reputation: 3865

Indexes for mongodb

I have a mongo db collection for restaurants. e.g. {_id: uniquemongoid, rank: 3, city: 'Berlin' }

  1. Restaurants are listed by city and ordered by rank (an integer) - should I create an index on city and rank, or city/rank compound? (I query by city and sort by rank)

  2. Furthermore there are several fields with booleans e.g. { hasParking:true, familyFriendly:true } - should I create indexes to speed up queries for these filters? compound indexes? Its not clear for me if I should create compound indexes as the queries can have only one boolean set or more booleans set.

Upvotes: 4

Views: 12410

Answers (2)

Derick
Derick

Reputation: 36794

The best way to figure out whether you need indexes is to benchmark it with "explain()".

As for your suggested indexes:

  1. You will need the city/rank compound index. Indexes in MongoDB can only be used for left-to-right (at the moment) and hence doing an equality search on "city" and then sorting the result by "rank" will mean that the { city: 1, rank: -1 } index would work best.

  2. Indexes on boolean fields are often not very useful, as on average MongoDB will still need to access half of your documents. After doing a selection by city (and hopefully a limit!) doing an extra filter for hasParking etc will not make MongoDB use both the city/rank and the hasParking index. MongoDB can only use one index per query.

Upvotes: 6

Abhishek Kumar
Abhishek Kumar

Reputation: 3366

1) create index { restaurant:1, rank: 1} which will serve your purpose.

You will avoid 2 indexes

2) Create a document in following format and you can query for any no of fields you want.

{
    info: [{hasParking:true}, {familyFriendly:true}],
    _id:
    rank:
    city:
}
db.restaurants.ensureIndex({info : 1});
db.restaurants.find({ info :{ hasParking:true}})
  • Note MongoDB don't use two index for the same query (except $or queries). So, in the (2) case, if you want to add addition filter over the (1) query, then this (2) option won't work. I am not sure of your (2) requirement, so posting this solution.

Upvotes: 2

Related Questions