Search code examples
mongodbmongodb-querymongodb-indexes

Which query faster on MongoDB with two index

I have two index in mongodb. First index field is "string" and second index field is "date". String field is partial. Why faster than I query "string" with "date" field when just "string"? How can I create an alternative?

In 200 million values:

First query : db.runCommand({explain:{count:"mycollection",query:{fruit:"apple"}}}); Query Result : {executionTimeMillis: 80,nCounted: 39509}

Second query : db.runCommand({explain:{count:"mycollection",query:{fruit:"apple",date:{$gte:ISODate('2022-03-22T00:00:00.000+00:00')}}}}); Query Result : {executionTimeMillis: 3402,nCounted: 22383}

Solution

  • I was using separately.

    As @R2D2 said:

    db.mycollection.createIndex({fruit: 1,date:-1},{partialFilterExpression:{fruit:{$exists: true }}});

    Using that method has made it much faster. Also indexes size almost fell by half.