CODEWITHSUNDEEP
mongodb
Tomas Hromnik
Tomas Hromnik

Reputation: 2200

How to create index for this particular query in MongoDB

I need to index this query:

db.messages.find({
  $or: [
    { $and: [
      { receiverFbId: 1 },
      { senderFbId: 2 }
    ]},
    { $and: [
      { receiverFbId: 2 },
      { senderFbId: 1 }
    ]}
  ]
}).sort({ timestamp: -1 });

I have created indexes:

db.messages.ensureIndex({ receiverFbId: 1 });
db.messages.ensureIndex({ senderFbId: 1 });
db.messages.ensureIndex({ receiverFbId: 1, senderFbId: 1, timestamp: -1 });

The first two indexes work for query whithout sorting by timestamp. The third index should work for query with sort but it doesn't. The query with explain() function returns BasicCursor.

So what index should I create to index this query with sort by timestamp?

Upvotes: 0

Views: 150

Answers (2)

Wizard
Wizard

Reputation: 4421

I made a test by db.messages.ensureIndex({ receiverFbId: 1, senderFbId: 1, timestamp: -1 }, {name:"rst"});. The index was used on MongoDB V2.6.4, but not used on V2.4.8.
So, perhaps you are out of luck if your MongoDB version is less than 2.6. :)

Otherwise, I want to say, it's almost impossible to use index to succeed on this query and sort by timestamp completely even on V2.6.4. Here I give an example.

Run below codes on mongo shell, (all are V2.6.4)

// initialize data
var docs = [ 
// group 1
{
    _id : 1,
    receiverFbId : 1,
    senderFbId : 2,
    timestamp : new Date("2014-10-09")
}, {
    _id : 2,
    receiverFbId : 1,
    senderFbId : 2,
    timestamp : new Date("2014-10-08")
}, {
    _id : 3,
    receiverFbId : 1,
    senderFbId : 2,
    timestamp : new Date("2014-10-07")
}, 
// group 2
{
    _id : 4,
    receiverFbId : 2,
    senderFbId : 1,
    timestamp : new Date("2014-10-08")
}, {
    _id : 5,
    receiverFbId : 2,
    senderFbId : 1,
    timestamp : new Date("2014-10-07")
}, {
    _id : 6,
    receiverFbId : 2,
    senderFbId : 1,
    timestamp : new Date("2014-10-09")
}, 
// group 3
{
    _id : 7,
    receiverFbId : 1,
    senderFbId : 8,
    timestamp : new Date("2014-10-09")
}, {
    _id : 8,
    receiverFbId : 2,
    senderFbId : 6,
    timestamp : new Date("2014-10-01")
} ];

var c = db["messages"];
c.drop();
c.insert(docs);
c.ensureIndex({ receiverFbId: 1, senderFbId: 1, timestamp: -1 }, {name: "rst"});

// make an output test
c.find({
      $or: [
        { $and: [
          { receiverFbId: 1 },
          { senderFbId: 2 }
        ]},
        { $and: [
          { receiverFbId: 2 },
          { senderFbId: 1 }
        ]}
      ]
    }).sort({ timestamp: -1 }); 
// result
{ "_id" : 1, "receiverFbId" : 1, "senderFbId" : 2, "timestamp" : ISODate("2014-10-09T00:00:00Z") }
{ "_id" : 6, "receiverFbId" : 2, "senderFbId" : 1, "timestamp" : ISODate("2014-10-09T00:00:00Z") }
{ "_id" : 2, "receiverFbId" : 1, "senderFbId" : 2, "timestamp" : ISODate("2014-10-08T00:00:00Z") }
{ "_id" : 4, "receiverFbId" : 2, "senderFbId" : 1, "timestamp" : ISODate("2014-10-08T00:00:00Z") }
{ "_id" : 3, "receiverFbId" : 1, "senderFbId" : 2, "timestamp" : ISODate("2014-10-07T00:00:00Z") }
{ "_id" : 5, "receiverFbId" : 2, "senderFbId" : 1, "timestamp" : ISODate("2014-10-07T00:00:00Z") }

// make an explain
c.find({
      $or: [
        { $and: [
          { receiverFbId: 1 },
          { senderFbId: 2 }
        ]},
        { $and: [
          { receiverFbId: 2 },
          { senderFbId: 1 }
        ]}
      ]
    }).sort({ timestamp: -1 }).explain();
// result
{
    "clauses" : [ {
        "cursor" : "BtreeCursor rst",
        "isMultiKey" : false,
        "n" : 3,
        "nscannedObjects" : 3,
        "nscanned" : 3,
        "scanAndOrder" : false,             // Attention on this line
        "indexOnly" : false,
        "nChunkSkips" : 0,
        "indexBounds" : {
            "receiverFbId" : [ [ 1, 1 ] ],
            "senderFbId" : [ [ 2, 2 ] ],
            "timestamp" : [ [ {
                "$maxElement" : 1
            }, {
                "$minElement" : 1
            } ] ]
        }
    }, {
        "cursor" : "BtreeCursor rst",
        "isMultiKey" : false,
        "n" : 3,
        "nscannedObjects" : 3,
        "nscanned" : 3,
        "scanAndOrder" : false,             // Attention on this line
        "indexOnly" : false,
        "nChunkSkips" : 0,
        "indexBounds" : {
            "receiverFbId" : [ [ 2, 2 ] ],
            "senderFbId" : [ [ 1, 1 ] ],
            "timestamp" : [ [ {
                "$maxElement" : 1
            }, {
                "$minElement" : 1
            } ] ]
        }
    } ],
    "cursor" : "QueryOptimizerCursor",
    "n" : 6,
    "nscannedObjects" : 6,
    "nscanned" : 6,
    "nscannedObjectsAllPlans" : 6,
    "nscannedAllPlans" : 6,
    "scanAndOrder" : false,                 // Attention on this line
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "server" : "Duke-PC:27017",
    "filterSet" : false
}

According to above output, it almost follows the expectation. But also we can find something else,

  • Both groups (group 1 and group 2) have been selected and sorted by index respectively.
  • But, these two groups have intersection on timestamp. To provide correct result, an extra global sorting in memory is necessary. This sorting should be very fast because each group has been ordered.

I understand the last line of "scanAndOrder" : false from the .explain() as it almost but not completely implements sorting without extra memory sorting.

--------------- Edit ------------------

CLARIFICATION

My previous comprehension about the last line of scanAndOrder : false in .explain() is wrong.
$or can perfectly merge results from those indexes, without extra burdened buffering.

Thanks for the help from Asya Kamsky.

Upvotes: 1

Anzel
Anzel

Reputation: 20553

It seems index on sort is really ONE direction only, meaning it can index sorting on the same direction (either ascending or descending), please refer to this. And you do not need to place multiple indexes for the same prefix. So, for your sort to work best, it should be just like this:

db.messages.ensureIndex({ receiverFbId: 1, senderFbId: 1 });

The sort must specify the same sort direction (i.e.ascending/descending) for all its keys as the index key pattern or specify the reverse sort direction for all its keys as the index key pattern. For example, an index key pattern { a: 1, b: 1 } can support a sort on { a: 1, b: 1 } and { a: -1, b: -1 } but not on { a: -1, b: 1 }.

So db.messages.ensureIndex({receiverFbId:1, senderFbId:1, timestamp:-1}) simply doesn't work.

and when you try to sort, use this: db.messages.find({...}).sort({timestamp: -1});

And to answer your question, no, currently it doesn't support indexing on the sort query you are suggesting. If you really insist to make this work, you have to rework your timestamp as something like (future - timestamp) to make it indexable on ascending so they can all be sorted in same direction.

Upvotes: 0

Related Questions