CODEWITHSUNDEEP
performancemongodbindexing
cutsoy
cutsoy

Reputation: 10251

MongoDB - Is searching in arrays as fast as searching in plain keys?

Say I've the following design:

id | participant_ids
...| [ObjectId(...), ObjectId(...)]

Now I'm querying it this way:

db.events.find({
    participant_ids: ObjectId(...)
});

Which is identical to this:

db.events.find({
    participant_ids: {
        $in: ObjectId(...)
    }
});

I assume there isn't a difference in performance between those two (but correct me if I'm wrong!).

For each event, there are at least 1 and at most 2 participants. So I could also use the following design:

id | participant_1_id | participant_2_id

... and query it like this ...:

db.events.find({
    $or: {
        participant_1_id: ObjectId(...),
        participant_2_id: ObjectId(...)
    }
});

If I wouldn't use indexing, this probably doesn't really make a difference, but -of course- I am.

For the first design, I'ld go with the following index:

db.events.ensureIndex({
    participant_ids: 1
});

For the second one, I'ld go with this:

db.events.ensureIndex({
    participant_1_id: 1,
    participant_2_id: 1
});

Both got downsides when you look at their performance.

My questions are: - What design should I use? - Can I index Arrays? The docs don't say anything about this, and I'm not sure Arrays are (since their contents can vary really much).

Upvotes: 6

Views: 2074

Answers (1)

Thilo
Thilo

Reputation: 262494

1st query: Using an Array is probably slower than using a plain key.

I don't think so. It should be the exact same index-based access path if you have one value ("plain key") or multiple ("Array").

participant_1_id, participant_2_id is just terrible.

Upvotes: 5

Related Questions