MongoDB indexes

Indexes

// user
db[ 'users' ].ensureIndex({
  'username': 1
});
// room
db[ 'rooms' ].ensureIndex({
  'name': 1
});
// room history
db[ 'history-room' ].ensureIndex({
  'room': 1,
  'time': -1
});
db[ 'history-room' ].ensureIndex({
  'event': 1
});
db[ 'history-room' ].ensureIndex({
  'room': 1,
  '_id': -1
});
// onetoone history
db[ 'history-one' ].ensureIndex({
  'from': 1,
  'to': 1,
  'time': -1
});
db[ 'history-one' ].ensureIndex({
  'event': 1
});
// notifications
db[ 'notifications' ].ensureIndex({
  'done': 1,
  'user': 1
});
db[ 'notifications' ].ensureIndex({
  'done': 1,
  'time': -1
});

HistoryRoom indexes comparison

/**
 * TESTED INDEX
 */
db[ 'history-room' ].ensureIndex({
  'name': 1,
  'time': -1
});

/**
 * EXPLAIN QUERIES
 */
db[ 'history-room' ].find({
  name: '#donut',
  users: { '$in': [ ObjectId('54198f09eb6b4ca00b578ecd') ] },
  event: { '$nin': [ 'user:online', 'user:offline' ] },
  time: {
    '$lte': new Date('Tue Nov 18 2014 16:28:11 GMT+0100 (Paris, Madrid)'),
    '$gte': new Date('Mon Nov 17 2014 00:00:00 GMT+0100 (Paris, Madrid)')
  }
}).sort({ time: -1 }).limit(500).explain();
db[ 'history-room' ].find({
  name: '#donut',
  users: { '$in': [ ObjectId('54198f09eb6b4ca00b578ecd') ] },
  event: { '$nin': [ 'user:online', 'user:offline' ] },
  time: {
    '$lte': new Date('Tue Nov 18 2014 16:28:11 GMT+0100 (Paris, Madrid)'),
    '$gte': new Date('Mon Nov 17 2014 00:00:00 GMT+0100 (Paris, Madrid)')
  }
}).sort({ time: -1 }).limit(500).hint({ $natural: 1 }).explain();

HistoryOne indexes comparison

/**
 * TESTED INDEX
 */
db[ 'history-one' ].ensureIndex({
  'from': 1,
  'to': 1,
  'time': -1
});

/**
 * EXPLAIN QUERIES
 */
db[ 'history-one' ].find({
  '$or': [
    {
      to: ObjectId('54285377bb6c3d101ec179cb'),
      from: ObjectId('53e69205962c67de3e4e9550')
    },
    {
      to: ObjectId('53e69205962c67de3e4e9550'),
      from: ObjectId('54285377bb6c3d101ec179cb')
    }
  ],
  event: { '$nin': [ 'user:online', 'user:offline' ] },
  time: {
    '$lte': new Date('Mon Nov 17 2014 00:00:00 GMT+0100 (Paris, Madrid)'),
    '$gte': new Date('Tue Nov 04 2014 12:30:22 GMT+0100 (Paris, Madrid)')
  }
}).sort({ time: -1 }).limit(500).hint({ $natural: 1 }).explain();
db[ 'history-one' ].find({
  '$or': [
    {
      to: ObjectId('54285377bb6c3d101ec179cb'),
      from: ObjectId('53e69205962c67de3e4e9550')
    },
    {
      to: ObjectId('53e69205962c67de3e4e9550'),
      from: ObjectId('54285377bb6c3d101ec179cb')
    }
  ],
  event: { '$nin': [ 'user:online', 'user:offline' ] },
  time: {
    '$lte': new Date('Mon Nov 17 2014 00:00:00 GMT+0100 (Paris, Madrid)'),
    '$gte': new Date('Tue Nov 04 2014 12:30:22 GMT+0100 (Paris, Madrid)')
  }
}).sort({ time: -1 }).limit(500).explain();

Messages number by day

db[ 'history-room' ].aggregate([
  { $match: { event: 'room:message' } },
  {
    $group: {
      _id: { month: { $month: '$time' }, day: { $dayOfMonth: '$time' } },
      count: { $sum: 1 }
    }
  }
])