title: MONGODB Simple query too long to be executed why?
body: <p>My query takes a lot of time ....</p>
  
  <ul>
  <li>130,000 documents -> 70ms (local)</li>
  <li>11,000 documents -> 80ms (local)</li>
  <li>1,600 documents -> 2ms (local) (other base, more complex query)</li>
  </ul>
  
  <p>Why do the first two put so much time compared to the third much more complex?</p>
  
  <p><strong>Query with 130.000 documents (60-69ms)</strong></p>
  
  <pre><code>{
      "_id" : ObjectId("58e669e81a5696098804e0ae"),
      "target" : "mysuperlogin",
      "type" : "auth",
      "key" : "try",
      "useragent" : "Mozilla/5.0 (iPhone; CPU iPhone OS 10_2_1 like Mac OS X) AppleWebKit/602.4.6 (KHTML, like Gecko) Mobile/14D27",
      "ip" : "X.X.X.X",
      "creation_date" : ISODate("2017-04-06T16:16:40.584Z"),
      "__v" : 0
  }
  
  db.collection.aggregate(
  [
      {
          $match:
          {
              'type':          'auth',
              'target':        params.authlogin,
              'ip':            'X.X.X.X',
              'creation_date': { $gte: date }
          },
          { $group: { _id: '$key', total: { $sum: 1 } } },
          { $sort:   { _id: -1 }}
      }
  ])
  </code></pre>
  
  <p><strong>Query with 11.000 documents (77-90ms)</strong></p>
  
  <pre><code>{
      "_id" : ObjectId("5a2a6b17aafee9dcf4000422"),
      "target" : "mysuperlogin",
      "type" : "auth",
      "key" : "try",
      "useragent" : "Mozilla/5.0 (iPhone; CPU iPhone OS 10_2_1 like Mac OS X) AppleWebKit/602.4.6 (KHTML, like Gecko) Mobile/14D27",
      "events" : [
          {
              "creation_date" : ISODate("2017-12-05T08:45:37.217Z"),
              "ip" : "X.X.X.X"
          },
          {
              "creation_date" : ISODate("2017-12-05T08:45:37.010Z"),
              "ip" : "X.X.X.X"
          },
          {
              "creation_date" : ISODate("2017-12-05T08:45:37.111Z"),
              "ip" : "X.X.X.X"
          },
          {
              "creation_date" : ISODate("2017-12-05T08:45:39.759Z"),
              "ip" : "X.X.X.X"
          },
          {
              "creation_date" : ISODate("2017-12-05T08:45:40.054Z"),
              "ip" : "X.X.X.X"
          },
          {
              "creation_date" : ISODate("2017-12-05T08:47:36.471Z"),
              "ip" : "X.X.X.X"
          }
      ]
  }
  
  db.collection.aggregate(
  [
      {
          $match:
          {
              'type':                 'auth',
              'target':               'mysuperlogin',
              'events.ip':            'X.X.X.X',
              'events.creation_date': { $gte: new Date('2017-12-05T08:30:00.471Z') }
          }
      },
      { $unwind: '$events' },
      { $match:  { 'events.creation_date': { $gte: new Date('2017-12-05T08:30:00.471Z') } } },
      { $group:  { _id: '$key', total: { $sum: 1 } } },
      { $sort:   { _id: -1 }}
  ])
  </code></pre>
  
  <p><strong>Query with 1.600 documents (3-4ms)</strong></p>
  
  <pre><code>{
      "_id" : ObjectId("58f0ce79e07b3e6c24c5a453"),
      "groups" : [
          {
              "name" : "defaut",
              "company_id" : ObjectId("58e4f31cf8eca22e06167736"),
              "apikey" : "XXXXXXXXXXXXXXXXXXXXXXXXXXX",
              "level" : 1,
              "expire_date" : ISODate("2018-12-31T00:00:00Z"),
              "creation_date" : ISODate("2017-04-14T13:28:25.122Z")
          }
      ],
      "sessions" : [
          {
              "apikey" : "XXXXXXXXXXXXXXXXXXXXXXXXXXX",
              "token" : "YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY",
              "ip" : "X.X.X.X",
              "expire_date" : ISODate("2017-05-14T13:35:13.533Z"),
              "_id" : ObjectId("58f0d0113675136beb64c5a1")
          },
          {
              "apikey" : "XXXXXXXXXXXXXXXXXXXXXXXXXXX",
              "tokenassets" : "YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY",
              "ip" : "X.X.X.X",
              "expire_date" : ISODate("2017-05-14T13:35:13.533Z"),
              "_id" : ObjectId("58f0d0113675136beb64c5a1")
          }
      ],
      "infos" : {
          "login" : "mysuperlogin",
          "mail" : "mysuperlogin@mymail.com",
          "password" : "CRYPTEDCRYPTEDCRYPTEDCRYPTEDCRYPTEDCRYPTEDCRYPTEDCRYPTEDCRYPTEDCRYPTEDCRYPTEDCRYPTEDCRYPTEDCRYPTEDCRYPTED",
          "firstname" : "my",
          "lastname" : "superlogin",
          "creation_date" : ISODate("2017-04-14T13:28:25.124Z")
      },
      "__v" : 0
  }
  
  db.collection.aggregate(
  [
      {
          $match:
          {
              $and:
              [
                  {$or:
                  [
                      {'infos.login': 'XXXXXXX'},
                      {'infos.mail':  'XXXXXXX'.toLowerCase()}
                  ]},
                  {$or:
                  [
                      {'infos.password': 'XXXXXXX'},
                      {
                          sessions:
                          {
                              $elemMatch:
                              {
                                  'apikey': 'XXXXXXX',
                                  'token':  'XXXXXXX'
                              }
                          }
                      },
                      {
                          sessions:
                          {
                              $elemMatch:
                              {
                                  'apikey': 'XXXXXXX',
                                  'tokenassets':  'XXXXXXX'
                              }
                          }
                      }
                  ]}
              ]
          }
      },
      {
          $lookup:
          {
              from: 'othercollection',
              localField: '_id',
              foreignField: 'user_id',
              as: 'othercollection'
          }
      },
      { $unwind: '$othercollection' },
      {
          $project: { 'groups._id': 0, 'othercollection.groups._id': 0 }
      }
  ])
  </code></pre>
  
  <p><strong>Schemas (create with mongoose nodejs) :</strong></p>
  
  <pre><code>// Collection 1 (130.000 docs)
  const historiesSchema = new mongoose.Schema(
  {
      user_id:    mongoose.Schema.Types.ObjectId,
      apikey:     String,
      ip:         String,
      target:     String,
      type:       String,
      key:        String,
      value:      String,
      level:      Number,
      useragent:  String,
      creation_date:
      {
          type:    Date,
          default: Date.now
      }
  });
  
  
  // Collection 2 (11.000 docs)
  const eventsSchema = new mongoose.Schema(
  {
      ip: String,
      creation_date:
      {
          type:    Date,
          default: Date.now
      }
  },
  {
      _id : false
  });
  const histories2Schema = new mongoose.Schema(
  {
      user_id:   mongoose.Schema.Types.ObjectId,
      apikey:    String,
      ip:        String,
      target:    String,
      type:      String,
      key:       String,
      value:     String,
      level:     Number,
      useragent: String,
      events:    [eventsSchema]
  });
  
  
  // Collection 3 (1.600 docs)
  const sessionSchema = new mongoose.Schema(
  {
      apikey:      String,
      token:       String,
      tokenassets: String,
      ip:          String,
      expire_date: Date
  },
  {
      _id : false
  });
  const userSchema = new mongoose.Schema(
  {
      infos:
      {
          login:       String,
          mail:        String,
          nomail:      Boolean,
          password:    String,
          firstname:   String,
          lastname:    String,
          suspend:     Boolean,
          creation_date:
          {
              type:    Date,
              default: Date.now
          }
      },
      lostpass:
      {
          token:         String,
          creation_date: Date
      },
      sessions: [sessionSchema]
  });
  </code></pre>