mongodb schema design queries to include lookups

user_612258 0
Viewed 538

I was asked this question in an interview and don't know the answer. "You are designing a monogdb schema to support queries to include lookups. What should you do?"

The options are

Create a multicolumn index on 
    a)  key value used as primary and foreign key
   b) foreign key and most unique column
[ b was my answer because i thought it would avoid collisions. Intereviewer disagreed, didn't tell me the answer.]
OR-
c) Create an index on the key value used as the foreign key
d) Create an index on the key value used as primary key

Interviewer wouldn't tell me more info. about the db. It was like he was reading from a book.

mongodb
1 Answers

I should choose "c". Not very sure, but let me share my line of thought.

Imagine that you have two collections, you will lookup the collection1's customer_id on collection2's customer_id, what MongoDB will do in the back-end?

For each customer_id in the collection1 it will try to find a match in collection2, and it can do it scanning each document in collection2 or using a memorized map of which documents contain the desired customer_id. If MongoDB knows exactly where to find the equivalent customer_id it is not going to need to scan any document.

But why not create an index on the primary key field? Because it's not going to be used, since MongoDB will pass through each document in the collection1 anyway.

These links might give you some direction:

user_108359840
Related