Indexing on single field

[nikhilece2011 (Nikhil Sharma)]

I have multiple fields in my collection for instance:- first_name, last_name, ssn etc.
Suppose I have created a index over ssn field.

db.collection.createIndex({ ssn : 1 })

If I will use ssn field in my query than my index will be used.

db.collection.find({ “ssn”: “XXX”})

But If I will write the query :- db.collection.find({“first_name”:“XXX”})
My index will not be used created over ssn field.
How we can overcome this problem because I can search on any field to get my data ?
Should I create index on each field ? Please guide.

[Natac13 (Sean Campbell)]

Hey @nikhilece2011

You are correct in everything you have stated.

How we can overcome this problem

There are now 2 ways. However as we learn in M201 and other courses, one of the most important considerations for index creation is the access patterns of the application(s) connected to the mongod.

because I can search on any field to get my data

So in saying that if you had 3 individual queries in your application like so:

db.collection.find({ ssn : 555-555-555 })
db.collection.find({ lastName: Simpson })
db.collection.find({ firstName: Bart })

Then yes I would say to create an index on each field.
However if your queries were as follows then you could use a compound index which will handle different situation.

db.collection.find({ lastName: 'Simpson' })
db.collection.find({ lastName: 'Simpson', firstName: 'Bart' })
db.collection.find({ lastName: 'Simpson', firstName: 'Bart' }).sort({ ssn: 1 })

Then one index could handle all 3

db.collection.createIndex({ lastName: 1, firstName: 1, ssn: 1 })

At the start I said there are now 2 ways.
In MongoDB 4.2 Wildcard Indexes were introduced.
https://docs.mongodb.com/manual/core/index-wildcard/#wildcard-indexes

[nikhilece2011 (Nikhil Sharma)]

Thanks @natac13. I also got my answer as I am moving forward in my course.