Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MongoDB $where queries and tailable cursors -- WAS: date math best practices

Tags:

mongodb

My problem: give me a list of documents older than X amount of time.

If I have a document created with:

db.dates.insert({date: new Date()});

And now I want to find it only when the "date" has become 30 minutes old:

db.dates.find({ $where: "this.date.getTime() + 30 * 60000 <= new Date()"});

This works, but in the Mongo documentation states quite clearly that there is a significant performance penalty to $where queries.

Thus the question, is there a better way?

==========UPDATE 1==========

I should have added that I am hoping to have this query function "dynamically" be creating the query one time and using it to obtain a tailable cursor on a capped collection... and I am not sure any longer that it is actually possible.

I will test and repost.

==========UPDATE 2==========

So, looks like my "delayed" queue is going to have to be handled in code, either with polling or some "check, then sleep" algorithm, because that appears to be what mongo's delayed replication is doing (from db.cpp):

if ( replSettings.slavedelay && ( unsigned( time( 0 ) ) < nextOpTime.getSecs() + replSettings.slavedelay ) ) {
    assert( justOne );
    oplogReader.putBack( op );
    _sleepAdviceTime = nextOpTime.getSecs() + replSettings.slavedelay + 1;
    dblock lk;
    if ( n > 0 ) {
        syncedTo = last;
        save();
    }
    log() << "repl:   applied " << n << " operations" << endl;
    log() << "repl:   syncedTo: " << syncedTo.toStringLong() << endl;
    log() << "waiting until: " << _sleepAdviceTime << " to continue" << endl;
    return okResultCode;
}
like image 328
Justin Coffey Avatar asked Oct 10 '22 07:10

Justin Coffey


1 Answers

The $lte operator (and other range queries) will work and utilize indexes, but it cannot evaluate expressions. You have to query against a query-time constant (which would be 'now - 30 min'):

var threshold = new Date(); 
threshold.setMinutes(-30);

// now, query against a constant:
db.dates.find({"date" : {$lte : threshold}});

Of course, you can do the same with any driver for any programming language, e.g. C#

var c = db.Collection.Find(Query.LTE("date", DateTime.UtcNow.AddMinutes(-30));
like image 57
mnemosyn Avatar answered Oct 13 '22 10:10

mnemosyn