Say I have the following design:
id | participant_ids ...| [ObjectId(...), ObjectId(...)]
Now I ask him like this:
db.events.find({ participant_ids: ObjectId(...) });
Which is identical to this:
db.events.find({ participant_ids: { $in: ObjectId(...) } });
I guess there is no performance difference between the two (but correct me if I am wrong!).
For each event, there must be at least 1 and no more than 2 participants. Therefore, I could also use the following design:
id | participant_1_id | participant_2_id
... and request it like this:
db.events.find({ $or: { participant_1_id: ObjectId(...), participant_2_id: ObjectId(...) } });
If I hadn't used indexing, this probably doesn't matter much, but of course I do.
For the first design, I went with the following index:
db.events.ensureIndex({ participant_ids: 1 });
For the second, I went with this:
db.events.ensureIndex({ participant_1_id: 1, participant_2_id: 1 });
Both got flaws when you look at their performance.
- 1st request: Using
Array is probably slower than using a simple key. - Second request: Using the
$or operator is not very fast. - 2nd request: Not very scalable, say, I would like to someday free up the limit of participants, this would be impossible (you would have unlimited keys and unlimited elements in
$or - part of the requests).
My questions are: - Which design should I use? - Is it possible to index Array s? The docs don't say anything about this, and I'm not sure if Array (as their contents can vary greatly).