Repository navigation
Dex doesn't recommend indexes when sort operation is provided #21
Description
Having the following collection:
> db.people.find()
{ "_id" : ObjectId("52779d40ad2f74e0deb3a7cf"), "name" : "Gerard", "age" : 31 }
{ "_id" : ObjectId("52779d83ad2f74e0deb3a7d0"), "name" : "Rose", "age" : 23 }
{ "_id" : ObjectId("52779d9ead2f74e0deb3a7d1"), "name" : "Yuki", "age" : 30 }
>
I tried some basic queries, for example:
> db.people.find({age:{$gte:30}}).sort({name:1})
> db.people.find({age:{$gte:30}})
Passing the DEX tool to an empty system.profile collection, I can see that there's a suggestion when issuing the second query, but not when using the first one.
With sort operation:
> db.system.profile.drop()
true
> db.setProfilingLevel(2)
{ "was" : 0, "slowms" : 100, "ok" : 1 }
> db.people.find({age:{$gte:30}}).sort({name:1})
{ "_id" : ObjectId("52779d40ad2f74e0deb3a7cf"), "name" : "Gerard", "age" : 31 }
{ "_id" : ObjectId("52779d9ead2f74e0deb3a7d1"), "name" : "Yuki", "age" : 30 }
> db.setProfilingLevel(0)
{ "was" : 2, "slowms" : 100, "ok" : 1 }
>
(env)gerard@antares:~/projects/dextest$ dex -p mongodb://localhost/test
{
'runStats': {
'linesRecommended': 0,
'linesProcessed': 1,
'linesPassed': 2
},
'results': []
}
(env)gerard@antares:~/projects/dextest$
Without sort operation:
> db.system.profile.drop()
true
> db.setProfilingLevel(2)
{ "was" : 0, "slowms" : 100, "ok" : 1 }
> db.people.find({age:{$gte:30}})
{ "_id" : ObjectId("52779d40ad2f74e0deb3a7cf"), "name" : "Gerard", "age" : 31 }
{ "_id" : ObjectId("52779d9ead2f74e0deb3a7d1"), "name" : "Yuki", "age" : 30 }
> db.setProfilingLevel(0)
{ "was" : 2, "slowms" : 100, "ok" : 1 }
>
(env)gerard@antares:~/projects/dextest$ dex -p mongodb://localhost/test
{
'runStats': {
'linesRecommended': 1,
'linesProcessed': 1,
'linesPassed': 2
},
'results': [
{
'queryMask': '{"$query":{"age":{"$gte":"<val>"}}}',
'namespace': 'test.people',
'recommendation': {
'index': '{"age": 1}',
'namespace': 'test.people',
'shellCommand': 'db["people"].ensureIndex({"age": 1}, {"background": true})'
},
'details': {
'count': 1,
'totalTimeMillis': 0,
'avgTimeMillis': 0
}
}
]
}
(env)gerard@antares:~/projects/dextest$
Activity
esedor commentedon Nov 4, 2013
Thanks for this detailed report! I'll take a look. One question: Are you working with the HEAD of this Dex repo, or the last release of Dex on pypi?
Sirtea commentedon Nov 6, 2013
Not sure, it was the pypi version, installed via pip in a virtualenv
srlowe commentedon Jul 29, 2015
Any update on this? I'm having the same issue