Helo,
I have a mongodb collection with around 20k documents. The deleted documents are stored in the collection as well, meaning they are "soft" deleted.
Now, i want to query the documents based on "status" key. The status can be either "open", "closed" or "deleted". I need the records with status as "closed"
I see that the document count fulfilling my criteria is 25 only. However, the documents scanned (after applying index) are 18k.
Hence, my query takes around 2 minutes to execute and many a time, it times out.
My first questions is: 1. Should a query executing on 20k documents take so much time? 20k isn't such a huge count right? 2. Can someone guide me with optimizing the query further, if it can be? Pushing the deleted records in a separate archive collection is the last thing i would like to do.
Here is my current query:
**
db.collectionname.find({$and: [{ $and: [{ status: {$ne: 'open'} },{ status: {$ne: 'deleted'} }] },
{ 'submittedDate': { $gte: new Date("2019-02-01T00:00:00.000Z"), $lte: new Date("2019-02-02T00:00:00.000Z") } }
] })**
You must have a single field index as {status: 1}. Replace the index with a compound index {status: 1, submittedDate: 1} will improve your performance. 20k is nothing if properly indexed. If you have only 3 status, replace your query as follows.
db.collectionname.find({status: 'closed',
'submittedDate': { $gte: new Date("2019-02-01T00:00:00.000Z"),
$lte: new Date("2019-02-02T00:00:00.000Z") }})