Indexing on single field

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.

Hey @nikhilece2011

You are correct in everything you have stated.

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.

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

1 Like

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