0

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}

Murat Aslan
  • 1
  • 1
  • is the result same if you create compound index on {fruiit:1,date:-1} and drop the {date:1} index ? – R2D2 Mar 22 '22 at 20:39

1 Answers1

0

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.

Murat Aslan
  • 1
  • 1