Skip to content

Instantly share code, notes, and snippets.

@mmRoshani

mmRoshani/mongo.md

Last active July 29, 2022 17:25
Show Gist options
  • Save mmRoshani/b6a11957b925f0d1b3ee8ca11609d2b9 to your computer and use it in GitHub Desktop.
Save mmRoshani/b6a11957b925f0d1b3ee8ca11609d2b9 to your computer and use it in GitHub Desktop.
Download ZIP
MongoDB help full knowledge
Raw
mongo.md

MongoDB help full knowledge

Before read

  • Don't pay attention to mongoDB configuration, all of them are default and i didn't change them.
  • I intentionally put some ! in the json result to be a hint for you.
  • Ask any question here or contact me via linkedin for conversation.

Indexes

note that we have a collection named contacts, lets find out what is out collection documents look like:

db.contacts.findOne()
{
"_id": {"$oid": "62e3caaf192a9e1ab6ebb76f"},
"cell": "30393606",
"dob": {
  "date": "1959-02-19T23:56:23Z",
  "age": 59
},
"email": "victor.pedersen@example.com",
"gender": "male",
"id": {
  "name": "CPR",
  "value": "506102-2208"
},
"location": {
  "street": "2156 stenbjergvej",
  "city": "billum",
  "state": "nordjylland",
  "postcode": 56649,
  "coordinates": {
    "latitude": "-29.8113",
    "longitude": "-31.0208"
  },
  "timezone": {
    "offset": "+5:30",
    "description": "Bombay, Calcutta, Madras, New Delhi"
  }
},
"login": {
  "uuid": "fbb3c298-2cea-4415-84d1-74233525c325",
  "username": "smallbutterfly536",
  "password": "down",
  "salt": "iW5QrgwW",
  "md5": "3cc8b8a4d69321a408cd46174e163594",
  "sha1": "681c0353b34fae08422686eea190e1c09472fc1f",
  "sha256": "eb5251e929c56dfd19fc597123ed6ec2d0130a2c3c1bf8fc9c2ff8f29830a3b7"
},
"name": {
  "title": "mr",
  "first": "victor",
  "last": "pedersen"
},
"nat": "DK",
"phone": "23138213",
"picture": {
  "large": "https://randomuser.me/api/portraits/men/23.jpg",
  "medium": "https://randomuser.me/api/portraits/med/men/23.jpg",
  "thumbnail": "https://randomuser.me/api/portraits/thumb/men/23.jpg"
  },
  "registered": {
    "date": "2004-07-07T22:37:39Z",
    "age": 14
  }
 }
db.contacts.explain().find({"dob.age": {$gt: 60}})
  {
  "command": {
    "find": "contacts",
    "filter": {
      "dob.age": {
        "$gt": 60
      }
    },
    "$db": "learn"
  },
  "explainVersion": "1",
  "ok": 1,
  "queryPlanner": {
    "namespace": "learn.contacts",
    "indexFilterSet": false,
    "parsedQuery": {
      "dob.age": {
        "$gt": 60
      }
    },
    "queryHash": "FC9E47D2",
    "planCacheKey": "A5FF588D",
    "maxIndexedOrSolutionsReached": false,
    "maxIndexedAndSolutionsReached": false,
    "maxScansToExplodeReached": false,
    "winningPlan": {
      "stage": "COLLSCAN",
      "filter": {
        "dob.age": {
          "$gt": 60
        }
      },
      "direction": "forward"
    },
    "rejectedPlans": []
  },
  "serverInfo": {
    "host": "DESKTOP-SSN5JQP",
    "port": 27017,
    "version": "5.0.6",
    "gitVersion": "212a8dbb47f07427dae194a9c75baec1d81d9259"
  }
}
db.contacts.explain("executionStats").find({"dob.age": {$gt: 60}})
  {
    "command": {
      "find": "contacts",
      "filter": {
        "dob.age": {
          "$gt": 60
        }
      },
      "$db": "learn"
    },
    "executionStats": {
      "executionSuccess": true,
      "nReturned": 1222,
      "executionTimeMillis": 4,
      "totalKeysExamined": 0,
      "totalDocsExamined": 5000,
      "executionStages": {
        "stage": "COLLSCAN",
        "filter": {
          "dob.age": {
            "$gt": 60
          }
        },
        "nReturned": 1222,
        "executionTimeMillisEstimate": 0,
        "works": 5002,
        "advanced": 1222,
        "needTime": 3779,
        "needYield": 0,
        "saveState": 5,
        "restoreState": 5,
        "isEOF": 1,
        "direction": "forward",
        "docsExamined": 5000
      }
    },
    "explainVersion": "1",
    "ok": 1,
    "queryPlanner": {
      "namespace": "learn.contacts",
      "indexFilterSet": false,
      "parsedQuery": {
        "dob.age": {
          "$gt": 60
        }
      },
      "maxIndexedOrSolutionsReached": false,
      "maxIndexedAndSolutionsReached": false,
      "maxScansToExplodeReached": false,
      "winningPlan": {
        "stage": "COLLSCAN",
        "filter": {
          "dob.age": {
            "$gt": 60
          }
        },
        "direction": "forward"
      },
      "rejectedPlans": []
    },
    "serverInfo": {
      "host": "DESKTOP-SSN5JQP",
      "port": 27017,
      "version": "5.0.6",
      "gitVersion": "212a8dbb47f07427dae194a9c75baec1d81d9259"
    }
  }
db.contacts.createIndex({"dob.age": 1})
  {
    "result": "dob.age_1"
  }
db.contacts.explain("executionStats").find({"dob.age": {$gt: 60}})
{
  "command": {
    "find": "contacts",
    "filter": {
      "dob.age": {
        "$gt": 60
      }
    },
    "$db": "learn"
  },
  "executionStats": {
    "executionSuccess": true,
    "nReturned": 1222,
    "executionTimeMillis": 1,
    "totalKeysExamined": 1222,
    "totalDocsExamined": 1222,
    "executionStages": {
      "stage": "FETCH",
      "nReturned": 1222,
      "executionTimeMillisEstimate": 0,
      "works": 1223,
      "advanced": 1222,
      "needTime": 0,
      "needYield": 0,
      "saveState": 1,
      "restoreState": 1,
      "isEOF": 1,
      "docsExamined": 1222,
      "alreadyHasObj": 0,
      "inputStage": {
        "stage": "IXSCAN",
        "nReturned": 1222,
        "executionTimeMillisEstimate": 0,
        "works": 1223,
        "advanced": 1222,
        "needTime": 0,
        "needYield": 0,
        "saveState": 1,
        "restoreState": 1,
        "isEOF": 1,
        "keyPattern": {
          "dob.age": 1
        },
        "indexName": "dob.age_1",
        "isMultiKey": false,
        "multiKeyPaths": {
          "dob.age": []
        },
        "isUnique": false,
        "isSparse": false,
        "isPartial": false,
        "indexVersion": 2,
        "direction": "forward",
        "indexBounds": {
          "dob.age": ["(60, inf.0]"]
        },
        "keysExamined": 1222,
        "seeks": 1,
        "dupsTested": 0,
        "dupsDropped": 0
      }
    }
  },
  "explainVersion": "1",
  "ok": 1,
  "queryPlanner": {
    "namespace": "learn.contacts",
    "indexFilterSet": false,
    "parsedQuery": {
      "dob.age": {
        "$gt": 60
      }
    },
    "maxIndexedOrSolutionsReached": false,
    "maxIndexedAndSolutionsReached": false,
    "maxScansToExplodeReached": false,
    "winningPlan": {
      "stage": "FETCH",
      "inputStage": {
        "stage": "IXSCAN",
        "keyPattern": {
          "dob.age": 1
        },
        "indexName": "dob.age_1",
        "isMultiKey": false,
        "multiKeyPaths": {
          "dob.age": []
        },
        "isUnique": false,
        "isSparse": false,
        "isPartial": false,
        "indexVersion": 2,
        "direction": "forward",
        "indexBounds": {
          "dob.age": ["(60, inf.0]"]
        }
      }
    },
    "rejectedPlans": []
  },
  "serverInfo": {
    "host": "DESKTOP-SSN5JQP",
    "port": 27017,
    "version": "5.0.6",
    "gitVersion": "212a8dbb47f07427dae194a9c75baec1d81d9259"
  }
}
db.contacts.dropIndex({"dob.age": 1})

note: use the indexes when trying to query on 20% 30% of your documents otherwise it will make query execution slower due to the extra step.

db.contacts.createIndex({"dob.age": 1, gender: 1})

Keep that in mind the compound indexes usage is left to right, for instance, if here we also have dob.age index that dosen't make sence becase we can normally use the dob.age from our compound index but if we query on gender that is a totally diffrent because gender items sorted based on the dob.age (categorically sorted). for more information and visualization see this link from mongoDB documentation.

{
  "result": "dob.age_1_gender_1"
}
db.contacts.explain().find({"dob.age": 35}).sort({gender: -1})

note that use big sort queries with indexe in case of mongodb 32MB memory limit.

{
  "command": {
    "find": "contacts",
    "filter": {
      "dob.age": 35
    },
    "sort": {
      "gender": -1
    },
    "$db": "learn"
  },
  "explainVersion": "1",
  "ok": 1,
  "queryPlanner": {
    "namespace": "learn.contacts",
    "indexFilterSet": false,
    "parsedQuery": {
      "dob.age": {
        "$eq": 35
      }
    },
    "queryHash": "712D0963",
    "planCacheKey": "536BFDBC",
    "maxIndexedOrSolutionsReached": false,
    "maxIndexedAndSolutionsReached": false,
    "maxScansToExplodeReached": false,
    "winningPlan": {
      "stage": "FETCH",
      "inputStage": {
        !"stage": "IXSCAN",
        "keyPattern": {
          "dob.age": 1,
          "gender": 1
        },
        "indexName": "dob.age_1_gender_1",
        "isMultiKey": false,
        "multiKeyPaths": {
          "dob.age": [],
          "gender": []
        },
        "isUnique": false,
        "isSparse": false,
        "isPartial": false,
        "indexVersion": 2,
        "direction": "backward",
        "indexBounds": {
          "dob.age": ["[35, 35]"],
          "gender": ["[MaxKey, MinKey]"]
        }
      }
    },
    "rejectedPlans": []
  },
  "serverInfo": {
    "host": "DESKTOP-SSN5JQP",
    "port": 27017,
    "version": "5.0.6",
    "gitVersion": "212a8dbb47f07427dae194a9c75baec1d81d9259"
  }
}
db.contacts.getIndexes()
{
  "key": {
    "_id": 1
  },
  "name": "_id_",
  "v": 2
},
{
  "key": {
    "dob.age": 1,
    "gender": 1
  },
  "name": "dob.age_1_gender_1",
  "v": 2
}
db.contacts.createIndex({email: 1}, {unique: true})
error> duplicated email for: { email: "abigail.clark@example.com" }

db.contacts.createIndex({"dob.age": 1}, {partialFilterExpression: {gender: "male"}})
{
  "result": "dob.age_1"
}

continue

db.contacts.explain().find({"dob.age": {$gt: 60}, gender: "male"})
{
  "command": {
    "find": "contacts",
    "filter": {
      "dob.age": {
        "$gt": 60
      },
      "gender": "male"
    },
    "$db": "learn"
  },
  "explainVersion": "1",
  "ok": 1,
  "queryPlanner": {
    "namespace": "learn.contacts",
    "indexFilterSet": false,
    "parsedQuery": {
      "$and": [
        {
          "gender": {
            "$eq": "male"
          }
        },
        {
          "dob.age": {
            "$gt": 60
          }
        }
      ]
    },
    "queryHash": "A5045964",
    "planCacheKey": "25A54F6A",
    "maxIndexedOrSolutionsReached": false,
    "maxIndexedAndSolutionsReached": false,
    "maxScansToExplodeReached": false,
    "winningPlan": {
      "stage": "FETCH",
      "filter": {
        "gender": {
          "$eq": "male"
        }
      },
      "inputStage": {
        !"stage": "IXSCAN",
        "keyPattern": {
          "dob.age": 1
        },
        "indexName": "dob.age_1",
        "isMultiKey": false,
        "multiKeyPaths": {
          "dob.age": []
        },
        "isUnique": false,
        "isSparse": false,
        "isPartial": true,
        "indexVersion": 2,
        "direction": "forward",
        "indexBounds": {
          "dob.age": ["(60, inf.0]"]
        }
      }
    },
    "rejectedPlans": []
  },
  "serverInfo": {
    "host": "DESKTOP-SSN5JQP",
    "port": 27017,
    "version": "5.0.6",
    "gitVersion": "212a8dbb47f07427dae194a9c75baec1d81d9259"
  }
}

continue

db.contacts.explain().find({"dob.age": {$gt: 60}})
{
  "command": {
    "find": "contacts",
    "filter": {
      "dob.age": {
        "$gt": 60
      }
    },
    "$db": "learn"
  },
  "explainVersion": "1",
  "ok": 1,
  "queryPlanner": {
    "namespace": "learn.contacts",
    "indexFilterSet": false,
    "parsedQuery": {
      "dob.age": {
        "$gt": 60
      }
    },
    "queryHash": "FC9E47D2",
    "planCacheKey": "2A8A02C7",
    "maxIndexedOrSolutionsReached": false,
    "maxIndexedAndSolutionsReached": false,
    "maxScansToExplodeReached": false,
    "winningPlan": {
      !"stage": "COLLSCAN",
      "filter": {
        "dob.age": {
          "$gt": 60
        }
      },
      "direction": "forward"
    },
    "rejectedPlans": []
  },
  "serverInfo": {
    "host": "DESKTOP-SSN5JQP",
    "port": 27017,
    "version": "5.0.6",
    "gitVersion": "212a8dbb47f07427dae194a9c75baec1d81d9259"
  }
}
db.test.insertMany([{name: "Ali"}, {name: "mmR", email: "mmR@mmr.mmr"}])
db.test.find()
{
  "_id": {"$oid": "62e3f18b7b328e52c2e7b6ac"},
  "name": "Ali"
},
{
  "_id": {"$oid": "62e3f18b7b328e52c2e7b6ad"},
  "name": "mmR",
  "email": "mmR@mmr.mmr"
}

continue

db.test.createIndex({email: 1}, {unique: true})
{
  "result": "email_1"
}

continue

db.test.insertOne({name: "anna" })
error> Write operation error on server localhost:27017. Write error: WriteError{code=11000, message='E11000 duplicate key error         collection: learn.test index: email_1 dup key: { email: null }', details={}}.

note that the null value is a unique value in defined index.

continue&fix:
db.test.dropIndex({email: 1})
db.test.createIndex({email: 1}, {unique: true, partialFilterExpression: {email: {$exists: true}}})
db.test.insertOne({name: "anna" })
[
  {
    "_id": {"$oid": "62e3f18b7b328e52c2e7b6ad"},
    "email": "mmR@mmr.mmr",
    "name": "mmR"
  },
  {
    "_id": {"$oid": "62e3f41e7b328e52c2e7b6b3"},
    "name": "anna"
  },
  {
    "_id": {"$oid": "62e3f4407b328e52c2e7b6b5"},
    "name": "Ali"
  }
]
and If:
db.test.insertOne({name: "tom", email: "mmR@mmr.mmr" })
error> Write operation error on server localhost:27017. Write error: WriteError{code=11000, message='E11000 duplicate key error         collection: learn.test index: email_1 dup key: { email: "mmR@mmr.mmr" }', details={}}.

note: just check duplicate for existence emails.

db.sessions.insertOne({name: "ttl" , createdAt: new Date})
db.sessions.createIndex({createdAt: 1}, {expireAfterSeconds: 15} )

note that by adding new value, all the collection will be affected.

efficient queries metrics

by using the "queryPlanner", "executionStats" or "allPlansExecution" as explain() given parameter, we can compare the values:

  • Miliseconds Process Time (lower is better)
  • IXSCAN vs. COLLSCAN
  • Number of keys examined (close to number of documents examined is better or 0)
  • Number of documents examined (close to number of documents returned is better or 0)
  • Number of documents returned

note that the item 3 and 5 also call "Covered Query"

db.customers.explain("executionStats").find({name: "sth"}, {_id:0, name: 1})

this will return the feilds from index and this is called "Covered Query stage" even if we can use compound indexes, if we can use the "Covered Query stage", it is a good idea (fast) to use it. (the returned documents is 'n' but the totalDocsExamined is '0' how cool is that )

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment