MongoDB query optimization

Question

as my current task I have to optimize the indexes so that some of our queries run faster. I read this article: http://emptysqua.re/blog/optimizing-mongodb-compound-indexes/
as this guys suggests My optimal query is when:

 nscanned= nscannedObject = n

Well, I was able to achieve that. The problem however that the query satisfying this formula is slower than the one does not ! Here is my explain output :

{
    "cursor" : "BtreeCursor previous_sticky_1",
    "nscanned" : 65019,
    "nscannedObjects" : 65019,
    "n" : 65019,
    "millis" : 5456,
    "nYields" : 76,
    "nChunkSkips" : 0,
    "isMultiKey" : false,
    "indexOnly" : false,
}//everything is ok ( nscanned= nscannedObject = n ) 



  {
"cursor" : "BtreeCursor subnetInfo.giAddr_1",
"nscanned" : 65023,
"nscannedObjects" : 65023,
"n" : 65019,
"scanAndOrder" : true,
"millis" : 3355,
"nYields" : 37,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false

} // nscanned = nscannedObject > n but , "millis = 3355 < 5456 " ???

Can you guys suggest why is that ? and which index to use ? subnetInfo.giAddr_1 or previous_sticky_1 ?

No one can help you before will not see your document and the query. — Denis
– Denis, Commented Aug 15, 2013 at 12:31

Ori Dar · Accepted Answer · 2013-08-15 12:22:13Z

3

You should try several executions of your query using both hints and calculate the average time it takes. Your results may have to do with the fact that during your first execution, index files had to be paged into resident memory. Use touch to eliminate this effect.

Your indexes metrics are similar, so I don't expect any big difference using one or the other. What may be more relevant, which you haven't mentioned is the fields you use in indexes.

edited Aug 15, 2013 at 12:22

answered Aug 15, 2013 at 12:01

Ori Dar

19.1k5 gold badges61 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sammaye Over a year ago

Yields is when the data is not in RAM, it doesn't mean there were write operations

Collectives™ on Stack Overflow

MongoDB query optimization

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related