Build an index on top of a document in a nested subdocument in mongodb
In the test library of mongodb:
> db.data.insert ({name: "1616", info: {url: "http://www.1616.net/",city:"beijing"}});
> db.data.insert ({name: "hao123", info: {url: "http://www.hao123.com/",city:"beijing"}});"
> db.data.insert ({name: "ll4la", info: {url: "http://www.114la.com/",city:"dongguan"}});"
two。 Create an index on the field info:
> db.data.ensureIndex ({info: 1})
Index query for 3.data table:
Rs0:PRIMARY > db.data.getIndexes ()
[
{
"v": 1
"key": {
"_ id": 1
}
"name": "_ id_"
"ns": "test.data"
}
{
"v": 1
"key": {
"info": 1
}
"name": "info_1"
"ns": "test.data"
}
]
4. The use of the index:
The following query can use the index of info:
> db.data.find ({info: {url: "http://www.1616.net/", city:" beijing "}})
> db.data.find ({info: {url: "http://www.1616.net/"}})
> db.data.find ({info: {city: "beijing"})
You can use query.explain () to view the use of the index:
Rs0:PRIMARY > db.data.find ({info: {city: "beijing"}}) .explain ()
{
"queryPlanner": {
"plannerVersion": 1
"namespace": "test.data"
"indexFilterSet": false
"parsedQuery": {
"info": {
"$eq": {
"city": "beijing"
}
}
}
"winningPlan": {
"stage": "FETCH"
"inputStage": {
"stage": "IXSCAN"
"keyPattern": {
"info": 1
}
"indexName": "info_1"
"isMultiKey": false
"isUnique": false
"isSparse": false
"isPartial": false
"indexVersion": 1
"direction": "forward"
"indexBounds": {
"info": [
"[{city:\" beijing\ "}, {city:\" beijing\ "}"
]
}
}
}
"rejectedPlans": []
}
"serverInfo": {
"host": "mycentos.WORKGROUP"
"port": 27017
"version": "3.2.8"
"gitVersion": "ed70e33130c977bda0024c125b56d159573dbaf0"
}
"ok": 1
}
But this kind of query doesn't work:
> db.data.find ({"info.city": "beijing"}); / / the fields must be in quotation marks
> db.data.find ({info.url: "..."})
For such query statements, only similar composite indexes can be used:
> db.data.ensureIndex ({"info.url": 1, "info.city": 1})
5. Combinatorial index
> db.data.ensureIndex ({"info.url": 1, "info.city": 1})
Index scanning can be used even when querying, contrary to the sort defined.
Rs0:PRIMARY > db.data.find ({"info.url": / http:*/i}) .sort ({"info.url":-1, "info.city":-1}) .explain ()
{
"queryPlanner": {
"plannerVersion": 1
"namespace": "test.data"
"indexFilterSet": false
"parsedQuery": {
"info.url": / http:*/i
}
"winningPlan": {
"stage": "FETCH"
"inputStage": {
"stage": "IXSCAN"
"filter": {
"info.url": / http:*/i
}
"keyPattern": {
"info.url": 1
"info.city": 1
}
"indexName": "info.url_1_info.city_1"
"isMultiKey": false
"isUnique": false
"isSparse": false
"isPartial": false
"indexVersion": 1
"direction": "backward"
"indexBounds": {
"info.url": [
"[/ http:*/i, / http:*/i]"
"({},\"\ "]"
]
"info.city": [
"[MaxKey, MinKey]"
]
}
}
}
"rejectedPlans": []
}
"serverInfo": {
"host": "mycentos.WORKGROUP"
"port": 27017
"version": "3.2.8"
"gitVersion": "ed70e33130c977bda0024c125b56d159573dbaf0"
}
"ok": 1
}
