Discussion:
Mongodb Index specifications
(too old to reply)
omongo
2017-03-22 12:32:10 UTC
Permalink
Hi,
I have three questions about Index specification.
Please advice for me

Here is my test collection

- data

{ "emp" : "a", "num" : 5.0, "lev" : 0.0}
{ "emp" : "b", "num" : 6.0, "lev" : 1.0}
{ "emp" : "c", "num" : 7.0, "lev" : 2.0}
{ "emp" : "d", "num" : 8.0, "lev" : 3.0}
{ "emp" : "e", "num" : 9.0, "lev" : 4.0}
{ "emp" : "f", "num" : 10.0, "lev" : 5.0}
{ "emp" : "g", "num" : 11.0, "lev" : 6.0}
{ "emp" : "h", "num" : 12.0, "lev" : 7.0}
{ "emp" : "i", "num" : 13.0, "lev" : 8.0}
{ "emp" : "j", "num" : 14.0, "lev" : 9.0}
{ "emp" : "k", "num" : 6.0, "lev" : 2.0}
{ "emp" : "l", "num" : 6.0, "lev" : 6.0}
{ "emp" : "k", "num" : 4.0, "lev" : 2.0}

- Index

{ "emp" :1 }
{ "num" :1 }
{ "lev" :1 }


1. Why can't mongodb use over two indexes for one query?
If I query "db.test.find({emp:"a",num:2,lev:3})",
I think mongodb use three of indexes(emp_1,num_1, lev_1) for this query.
But explain describe like below.
Query doesn't have plan that use three indexes. Why can't I use three
indexes?
Is my query wrong?
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"lev" : {
"$eq" : 2.0
}
},
{
"num" : {
"$eq" : 6.0
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"emp" : 1.0
},
"indexName" : "emp_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"emp" : [
"[\"k\", \"k\"]"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"emp" : {
"$eq" : "k"
}
},
{
"lev" : {
"$eq" : 2.0
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"num" : 1.0
},
"indexName" : "num_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"num" : [
"[6.0, 6.0]"
]
}
}
},
{
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"emp" : {
"$eq" : "k"
}
},
{
"num" : {
"$eq" : 6.0
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"lev" : 1
},
"indexName" : "lev_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"lev" : [
"[2.0, 2.0]"
]
}
}
},
{
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"num" : {
"$eq" : 6.0
}
},
{
"lev" : {
"$eq" : 2.0
}
},
{
"emp" : {
"$eq" : "k"
}
}
]
},
"inputStage" : {
"stage" : "AND_SORTED",
"inputStages" : [
{
"stage" : "IXSCAN",
"keyPattern" : {
"num" : 1.0
},
"indexName" : "num_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"num" : [
"[6.0, 6.0]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"lev" : 1
},
"indexName" : "lev_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"lev" : [
"[2.0, 2.0]"
]
}
}
]
}
},
{
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"emp" : {
"$eq" : "k"
}
},
{
"num" : {
"$eq" : 6.0
}
},
{
"lev" : {
"$eq" : 2.0
}
}
]
},
"inputStage" : {
"stage" : "AND_SORTED",
"inputStages" : [
{
"stage" : "IXSCAN",
"keyPattern" : {
"emp" : 1.0
},
"indexName" : "emp_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"emp" : [
"[\"k\", \"k\"]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"num" : 1.0
},
"indexName" : "num_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"num" : [
"[6.0, 6.0]"
]
}
}
]
}
},
{
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"emp" : {
"$eq" : "k"
}
},
{
"lev" : {
"$eq" : 2.0
}
},
{
"num" : {
"$eq" : 6.0
}
}
]
},
"inputStage" : {
"stage" : "AND_SORTED",
"inputStages" : [
{
"stage" : "IXSCAN",
"keyPattern" : {
"emp" : 1.0
},
"indexName" : "emp_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"emp" : [
"[\"k\", \"k\"]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"lev" : 1
},
"indexName" : "lev_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"lev" : [
"[2.0, 2.0]"
]
}
}
]
2. When I use ranged query, Mongo can only use one index?
When I query that doesn't have range, mongodb can use two of indexs like
below.

*
db.getCollection('test').find({"num":6, lev:2}).explain()
(this query use this plan, but reject)
"inputStage" : {
"stage" : "AND_SORTED",
"inputStages" : [
{
"stage" : "IXSCAN",
"keyPattern" : {
"num" : 1.0
},
"indexName" : "num_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"num" : [
"[6.0, 6.0]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"lev" : 1
},
"indexName" : "lev_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"lev" : [
"[2.0, 2.0]"
]
}
}
]
But If I add range like "db.getCollection('test').find({"num":6,
lev:{$gte:2,$lt:3}}).explain()",
this query should use only one index(see below. this is all of query
plan)
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"num" : {
"$eq" : 6.0
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"lev" : 1
},
"indexName" : "lev_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"lev" : [
"[2.0, 3.0)"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"lev" : {
"$lt" : 3.0
}
},
{
"lev" : {
"$gte" : 2.0
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"num" : 1.0
},
"indexName" : "num_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"num" : [
"[6.0, 6.0]"
]
}
}
}
]
3.I can't Understand this manual! I confirmed mongodb can use two of
indexes like above.
< Generally, MongoDB only uses one index to fulfill most queries.
However, each clause of an $or query may use a different index,
and starting in 2.6, MongoDB can use an intersection of multiple
indexes. >

I read manual about indexing Strategies
<https://docs.mongodb.com/manual/applications/indexes/>(see above), and I
found mongodb only use one index per one query.
Is it right? Did I misunderstand?
I don't think "can use an intersection of multiple indexes" means mongodb
can use several index for one query.
If I query "db.test.find({emp:"a",num:2,lev:3}),
index optimizer should choose only one index ?(ex :{ "emp" :1 } or
{ "num" :1 } or { "lev" :1 })

Any help much appreciated
Thanks.

Best,
Lee
--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.

For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+***@googlegroups.com.
To post to this group, send email to mongodb-***@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/f765fd31-3de5-4113-912f-c6f27283573d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
'Rhys Campbell' via mongodb-user
2017-03-22 15:09:05 UTC
Permalink
Yes, generally only one excluding the exception you have noted.

Using an intersection of indexes will often not be the best result as it
will have to perform multiple index scans. Many databases can do this in
theory but in practice I have not seen it much. MongoDB is probably
deciding that using 2 indexes is not the best plan. Using additional
indexes does not automatically mean the query would perform better.

For your query you should give it a better index...

db.test.find({emp:"a",num:2,lev:3})


This would be...

db.test.createIndex({ "emp": 1, "num": 1, "lev": 1 });
--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.

For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+***@googlegroups.com.
To post to this group, send email to mongodb-***@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/659f64e1-cb60-41ef-ab1e-efc7bf033792%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
omongo
2017-03-23 00:54:12 UTC
Permalink
Thank you, Rhys.

And then, If I have data over billion(big data) and use serveral kinds of
query,
Should I make compond keys that covered each query?
I don't think it is *not appropriate* to use *one index* for index scan and
filter for another field

Here is my example.
If I use these query , Should I make these indexes below?
" emp_1_num_1_lev_1", "num_1_lev_1", "num_1_tmpfield_1", "
emp_1_num_1_tmpfield_1"
These indexes' size will be too big to get good write performance.
Do I have no choice to read performance?
db.test.find({emp:"a"}) => index: emp_1_num_1_lev_1
db.test.find({num:2}) => index: num_1_lev_1
db.test.find({emp:"a",num:2}) => index: emp_1_num_1_lev_1
db.test.find({num:2,lev:3}) => index: num_1_lev_1
db.test.find({emp:"a",num:2,lev:3}) => index: emp_1_num_1_lev_1
db.test.find({num:2,tmpfield:3}) => index: num_1_tmpfield_1
db.test.find({emp:"a",num:2,tmpfield:3}) => index: emp_1_num_1_tmpfield_1

Or, Can I use over 2 indexes by force?( failed to apply
hint("emp_1","num_1"))
In case I have indexes "emp_1_num_1", "num_1", tmpfield_v1", "lev_1",Can I
use like below?
db.test.find({emp:"a"}) => index: emp_1_num_1
db.test.find({num:2}) => index: num_1
db.test.find({emp:"a",num:2}) => index: emp_1_num_1
db.test.find({num:2,lev:3}) => index: num_1 , lev_1
(2 indexes)
db.test.find({emp:"a",num:2,lev:3}) => index: emp_1_num_1 ,
lev_1 (2 indexes)
db.test.find({num:2,tmpfield:3}) => index: num_1 ,
tmpfield_1 (2 indexes)
db.test.find({emp:"a",num:2,tmpfield:3}) => index: emp_1_num_1 ,
tmpfield_1 (2 indexes)


Best,
Lee
Post by omongo
Hi,
I have three questions about Index specification.
Please advice for me
Here is my test collection
- data
{ "emp" : "a", "num" : 5.0, "lev" : 0.0}
{ "emp" : "b", "num" : 6.0, "lev" : 1.0}
{ "emp" : "c", "num" : 7.0, "lev" : 2.0}
{ "emp" : "d", "num" : 8.0, "lev" : 3.0}
{ "emp" : "e", "num" : 9.0, "lev" : 4.0}
{ "emp" : "f", "num" : 10.0, "lev" : 5.0}
{ "emp" : "g", "num" : 11.0, "lev" : 6.0}
{ "emp" : "h", "num" : 12.0, "lev" : 7.0}
{ "emp" : "i", "num" : 13.0, "lev" : 8.0}
{ "emp" : "j", "num" : 14.0, "lev" : 9.0}
{ "emp" : "k", "num" : 6.0, "lev" : 2.0}
{ "emp" : "l", "num" : 6.0, "lev" : 6.0}
{ "emp" : "k", "num" : 4.0, "lev" : 2.0}
- Index
{ "emp" :1 }
{ "num" :1 }
{ "lev" :1 }
1. Why can't mongodb use over two indexes for one query?
If I query "db.test.find({emp:"a",num:2,lev:3})",
I think mongodb use three of indexes(emp_1,num_1, lev_1) for this query.
But explain describe like below.
Query doesn't have plan that use three indexes. Why can't I use three
indexes?
Is my query wrong?
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"lev" : {
"$eq" : 2.0
}
},
{
"num" : {
"$eq" : 6.0
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"emp" : 1.0
},
"indexName" : "emp_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"emp" : [
"[\"k\", \"k\"]"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"emp" : {
"$eq" : "k"
}
},
{
"lev" : {
"$eq" : 2.0
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"num" : 1.0
},
"indexName" : "num_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"num" : [
"[6.0, 6.0]"
]
}
}
},
{
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"emp" : {
"$eq" : "k"
}
},
{
"num" : {
"$eq" : 6.0
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"lev" : 1
},
"indexName" : "lev_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"lev" : [
"[2.0, 2.0]"
]
}
}
},
{
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"num" : {
"$eq" : 6.0
}
},
{
"lev" : {
"$eq" : 2.0
}
},
{
"emp" : {
"$eq" : "k"
}
}
]
},
"inputStage" : {
"stage" : "AND_SORTED",
"inputStages" : [
{
"stage" : "IXSCAN",
"keyPattern" : {
"num" : 1.0
},
"indexName" : "num_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"num" : [
"[6.0, 6.0]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"lev" : 1
},
"indexName" : "lev_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"lev" : [
"[2.0, 2.0]"
]
}
}
]
}
},
{
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"emp" : {
"$eq" : "k"
}
},
{
"num" : {
"$eq" : 6.0
}
},
{
"lev" : {
"$eq" : 2.0
}
}
]
},
"inputStage" : {
"stage" : "AND_SORTED",
"inputStages" : [
{
"stage" : "IXSCAN",
"keyPattern" : {
"emp" : 1.0
},
"indexName" : "emp_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"emp" : [
"[\"k\", \"k\"]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"num" : 1.0
},
"indexName" : "num_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"num" : [
"[6.0, 6.0]"
]
}
}
]
}
},
{
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"emp" : {
"$eq" : "k"
}
},
{
"lev" : {
"$eq" : 2.0
}
},
{
"num" : {
"$eq" : 6.0
}
}
]
},
"inputStage" : {
"stage" : "AND_SORTED",
"inputStages" : [
{
"stage" : "IXSCAN",
"keyPattern" : {
"emp" : 1.0
},
"indexName" : "emp_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"emp" : [
"[\"k\", \"k\"]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"lev" : 1
},
"indexName" : "lev_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"lev" : [
"[2.0, 2.0]"
]
}
}
]
2. When I use ranged query, Mongo can only use one index?
When I query that doesn't have range, mongodb can use two of indexs like
below.
*
db.getCollection('test').find({"num":6, lev:2}).explain()
(this query use this plan, but reject)
"inputStage" : {
"stage" : "AND_SORTED",
"inputStages" : [
{
"stage" : "IXSCAN",
"keyPattern" : {
"num" : 1.0
},
"indexName" : "num_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"num" : [
"[6.0, 6.0]"
]
}
},
{
"stage" : "IXSCAN",
"keyPattern" : {
"lev" : 1
},
"indexName" : "lev_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"lev" : [
"[2.0, 2.0]"
]
}
}
]
But If I add range like "db.getCollection('test').find({"num":6,
lev:{$gte:2,$lt:3}}).explain()",
this query should use only one index(see below. this is all of query
plan)
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"num" : {
"$eq" : 6.0
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"lev" : 1
},
"indexName" : "lev_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"lev" : [
"[2.0, 3.0)"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"lev" : {
"$lt" : 3.0
}
},
{
"lev" : {
"$gte" : 2.0
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"num" : 1.0
},
"indexName" : "num_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"num" : [
"[6.0, 6.0]"
]
}
}
}
]
3.I can't Understand this manual! I confirmed mongodb can use two of
indexes like above.
< Generally, MongoDB only uses one index to fulfill most queries.
However, each clause of an $or query may use a different index,
and starting in 2.6, MongoDB can use an intersection of multiple
indexes. >
I read manual about indexing Strategies
<https://docs.mongodb.com/manual/applications/indexes/>(see above), and I
found mongodb only use one index per one query.
Is it right? Did I misunderstand?
I don't think "can use an intersection of multiple indexes" means mongodb
can use several index for one query.
If I query "db.test.find({emp:"a",num:2,lev:3}),
index optimizer should choose only one index ?(ex :{ "emp" :1 } or
{ "num" :1 } or { "lev" :1 })
Any help much appreciated
Thanks.
Best,
Lee
--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.

For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+***@googlegroups.com.
To post to this group, send email to mongodb-***@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/a0f155d2-7e3b-487d-abe0-c9c21f78634f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
'Rhys Campbell' via mongodb-user
2017-03-23 09:12:02 UTC
Permalink
Hi Lee,

It's not possible to give specific advice as it depends on your data. You
should test each approach but in general the compund index approach I would
expect to be better in most cases.

I don't really think you'll bit hit by write performance problems...
usually read is the issue long before write but you could try merging some
indexes if that becomes an issue. For example...

" emp_1_num_1_lev_1"
"emp_1_num_1_tmpfield_1"

Can probably be merged into..

emp1_num_1_tmpfield_1_lev1

Experiment with last 2 order. This will probably reduce selectivelty a bit
but it may a good comprimise to make if you have write problems.

If I recall correctly you can only hint one index (even this applies for
$or queries unfortunately)

Rhys
--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.

For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+***@googlegroups.com.
To post to this group, send email to mongodb-***@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/39f76ac6-1012-4ad9-9e22-1857076b5103%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
omongo
2017-03-23 10:27:17 UTC
Permalink
Thanks Rhys :)

You advice me
" emp_1_num_1_lev_1", "emp_1_num_1_tmpfield_1"
index can be merged into "emp1_num_1_tmpfield_1_lev1".

But I know that compound key support only beginning subsets of indexed field.

If I have "emp1_num_1_tmpfield_1_lev1" index,
it support these set
"emp1" / "emp1_num1" / "emp1_num1_tmpfield1" / "emp1_num1_tmpfield1_ lev1"
not "emp_1_num_1_lev_1"!!


I want to make index to use target query and query many different combination of indexed field.
Please give me a hand.
--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.

For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+***@googlegroups.com.
To post to this group, send email to mongodb-***@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/0e5d3516-cb42-435d-a80f-5c724db39725%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
'Rhys Campbell' via mongodb-user
2017-03-23 10:38:29 UTC
Permalink
But I know that compound key support only beginning subsets of indexed
field.

Yes that's true

"emp1" / "emp1_num1" / "emp1_num1_tmpfield1" /
"emp1_num1_tmpfield1_ lev1"

- All matched 100% by emp1_num_1_tmpfield_1_lev1


- emp_1_num_1_lev_1 -> Matches first 2, but not lev1.. However, as this is
part of the index it is still in memory and MAY offer a performance
increase in the case where lev_1 is not in the index. This advice is only
given on the basis that you want to reduce the total number of indexes. It
is a compromise. The optimal index for this would be emp_1_num_1_lev_1 as
you know.

I would start with indexes optimised for reads and only consider the above
if writes are poor. You need to test approaches.
--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.

For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+***@googlegroups.com.
To post to this group, send email to mongodb-***@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/65abe308-acee-4b33-93d2-116a669f3997%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Continue reading on narkive:
Loading...