Mongodb Index & mongoose-CodePudding

I have an update query that i need to run, and I have noticed it is slow.

 const nowplayingData = {"type":"S","station": req.params.stationname, "song": data[1], "artist": data[0], "timeplay":npdate};
                              LNowPlaying.findOneAndUpdate(
                                  nowplayingData,
                                  { $addToSet: { history: [uuid] } }, { upsert: true }, function(err) {
                                  if (err) {
                                      console.log('ERROR when submitting round');
                                      console.log(err);
                                  }
                              });

the document looks like this

{
  "_id": {
    "$oid": "5f117ae15b43e19dabd5ffb0"
  },
  "artist": "Ewa Kupec Piano",
  "song": "Nocturne In E-Flat Major, Op 7 Fourth Movement (Rondo",
  "station": "1lifeRadio",
  "timeplay": {
    "$date": "2020-07-17T11:19:00Z"
  },
  "type": "S",
  "__v": 0,
  "history": [
    "7320564F-76B2-40D0-A0E8-E3917148F567"
  ]
}

I have created the index in Mongodb compass app

index created

However I have noticed it's not using the index to when updating. do I need to do something inside MongooseJS to get it to use the index?

My schema is

  const StationNowPlayingSchema = new mongoose.Schema({
    station: {
        type: String,
       // index: { unique: true }
      },
    artist: {
        type: String,
       // index: { unique: true }
      },
    song: {
        type: String,
       // index: { unique: true }
      },
    timeplay: {
        type: Date,
    },
    type:{ type: String},
    cover:{ type: String},
    url:{ type: String},
    history:[],

  }, { collection: 'NowPlaying'});

CodePudding user response：

It is not very clear if you have created individual indexes or one compound index , but assuming you have compound index it seems your search order to not map with the created index:

   "type":X,"station": Y, "song": Z, "artist":M, "timeplay":N

Index:

 artist,song,station,type,timeplay

Based on your compound index following search order combinations are the ones that need to be used:

artist,song,station,type,timeplay
artist,song,station,type
artist,song,station
artist,song
artist

And -> Yes -> the order those metter! :)

But my advice is to use for the index the most selective field only and analyze your search queries or at least the ones used the most and create indexes according to those queries ...