Indexes for mongodb

3
votes

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.

2
performancemongodbindexing
It all depends how much data you have and what your queries are. - Thilo
Note that you can use the explain() method of cursor to check which index was used to fulfill a query. So when you aren't sure if a given index helps a specific query, you can try it out and check. - Philipp
My coworker gave a talk on this: youtube.com/watch?v=AVNjqgf7zNw He uses mysql as an example but the same principles apply. Maybe it will help you design your indexes. - leif

2 Answers

4
votes

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.

2
votes

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.