CODEWITHSUNDEEP
performancemongodbindexing
john smith
john smith

Reputation: 561

many indexes for mongodb refined searches

Referring to this question here:

I am working on a similar site using mongodb as my main database. As you can imagine, each user object has a lot of fields that need to be serchable, say for example mood, city, age, sex, smoker, drinker, etc.

Now, apart from the problem that there cannot be more than 64 indexes per collection, is it wise to assign index to all of my fields?

There might be another viable way of doing it: tags (refer to this other question) If i set the index on an array of predetermined tags and then text-search over them, would it be better? as I am using only ONE index. What do you think? E.g.:

{
   name: "john",
   tags: ["happy", "new-york", "smoke0", "drink1"]
}

Upvotes: 2

Views: 498

Answers (1)

mnemosyn
mnemosyn

Reputation: 46331

MongoDB doesn't (yet) support index intersection, so the rule is: one index per query. Some of your query parameters have extremely low selectivity, the extreme example being the boolean ones, and indexing those will usually slow things down rather than speed them up.

As a simple approximation, you could create a compound index that starts with the highest-selectivity fields, for instance {"city", "age", "mood", ... }. However, then you will always have to use a city constraint. If you query for {age, mood}, the above index wouldn't be used.

If you can narrow down your result set to a reasonable size using indexes, a scan within that set won't be a performance hog. More precisely, if you say limit(100) and MongoDB has to scan 200 items to fill up those 100, it won't be critical.

The danger lies is very narrow searches across the database - if you have to perform a scan on the entire dataset to find the only unhappy, drinking non-smoker older than 95, things get ugly.

If you want to allow very fine grained searches, a dedicated search database such as SolR might be a better option.

EDIT: The tags suggestion looks a bit like using the crowbar to me -- maybe the key/value multikey index recommended by in the MongoDB FAQ is a cleaner solution:

{ _id : ObjectId(...),
  attrib : [
            { k: "mood", v: "happy" },
            { k: "city": v: "new york" },
            { k: "smoker": v: false },
            { k: "drinker": v: true }
           ]
}

However, YMMV and 'clean' and 'fast' often don't point in the same direction, so the tags approach might not be bad at all.

Upvotes: 2

Related Questions