$where does not use indexes for find operation

I am trying to use $where in my script to run a find query, but the query doesn’t utilize the index to fulfill it. Whereas, if I run the find operation in a typical way, it uses the index fine. Is there any particular reason as to why this is happening? Please find the examples below to look at the execution stats.

  1. db.events.find({ $where: function () { return this.date_created < new Date(new ISODate() - 5000);} }).explain("allPlansExecution")
executionStats: {
    executionSuccess: true,
    nReturned: 306047,
    executionTimeMillis: 17624,
    totalKeysExamined: 0,
    totalDocsExamined: 306053,
    executionStages: {
      stage: 'COLLSCAN',
      filter: {
        '$where': Code("function () {
              return this.date_created < new Date(new ISODate() - 5000);
            }", {})
      },
      nReturned: 306047,
      executionTimeMillisEstimate: 17540,
      works: 306055,
      advanced: 306047,
  1. db.events.find({date_created:{$lt: new Date(new ISODate() - 5000)}}).explain("allPlansExecution")
executionStats: {
    executionSuccess: true,
    nReturned: 306061,
    executionTimeMillis: 586,
    totalKeysExamined: 306061,
    totalDocsExamined: 306061,
    executionStages: {
      stage: 'FETCH',
      nReturned: 306061,
      executionTimeMillisEstimate: 489,
      works: 306062,
      advanced: 306061,
      needTime: 0,
      needYield: 0,
      saveState: 2391,
      restoreState: 2391,
      isEOF: 1,

Any help will be greatly appreciated. Please feel free to throw your ideas even if you aren’t sure. Thank you.

Welcome to the MongoDB Community Forums @Anurag_Singh1!

The $where operator cannot take advantage of indexes so I strongly recommend using native query or aggregation operators instead:

The $where provides greater flexibility, but requires that the database processes the JavaScript expression or function for each document in the collection.

I’m not sure if this is the intended outcome of your query, but a Date value represents milliseconds since the Unix epoch so this query would find documents created more than 5 seconds ago and is effectively a collection scan even with an index (although much more efficient than evaluating each document in a JavaScript context).

Regards,
Stennie

1 Like

Hello Stennie, Thank you for your response. Thanks for explaining $where. Our backend guys are trying to delete the event documents based on the time to live value present on each document, and that’s why they are using $where. Please look at the snippet to get more context.

RemoveAll(bson.M{
“$where”: “this.date_created < ((new ISODate()-(this.event_time_to_live*1000)))”,
})

I think I have to find an alternative to performing this operation as this query is run every hour, so I have been getting this error Query Targeting: Scanned Objects / Returned has gone above 1000 every hour since it is not using indexes on that collection. Let me know if you have any ideas on that; if not, Thanks again for all the help. I really appreciate it.

How come your back-end guys are not using TTL Index for that purpose.

2 Likes

Hello Steeve, Thanks for that info. They acted smart, but thanks to you, now I have a bragging right!

2 Likes

Update
TTL indexes cannot be used in this situation as the expireAfterSeconds value is not the same for every document in that collection; instead, it is based on the time_to_live value within each document and should expire based on that time. Let me know your thoughts.

If time_to_live is known at document creation; it would make sense that it is, I am pretty sure a simple calculation can be done in order to be able to use a TTL index.

Let say you set your TTL index,

{ ttl_field : 1 } , { expireAfterSeconds: 3600 }

And you want to insert a document with a time_to_live of 4000 (400 seconds longer), you then insert your document with

`ttl_field : date_created + 400

So you get your extra 400 seconds.

The goal is really to be able to use TTL indexes as it must be most efficient way to delete expired documents. The implementation probably looks like:

min_key = MinKeyOfIndex
max_key = $$NOW + expireAfterSeconds
for ( key = min_key ; key <= max_key ; key = next key )
{
  delete document with key
}

It becomes a bounded index scan.

Hi @Anurag_Singh1,

There are two was to use TTL indexes:

In the latter case you set the TTL expireAfterSeconds value to 0, for example:

db.log_events.createIndex( { "expireAt": 1 }, { expireAfterSeconds: 0 } )

You can update the TTL date value in a document to extend or reduce the per-document expiry time. Documents that do not have a valid Date value will not expire.

NOTE: The background task that removes expired documents runs every 60 seconds, so expired documents will remain in a collection until the background task removes them.

Regards,
Stennie

2 Likes

Thanks a lot, Stennie! I really appreciate the help.

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.