Indexing MongoDB for quick search () using sort (), in different fields

Question

I have launched a lot of queries of this type:

db.mycollection.find({a:{$gt:10,$lt:100}, b:4}).sort({c:-1, a:-1})

What index should I use to speed it up? I think I need to have both {a:1, b:1}, and {c:-1, a:-1}, right? Or will these indices somehow interfere with each other in the absence of performance?

EDIT: The actual problem for me is that I run a lot of queries in a loop, some of them at a short distance, others at a wide range. If you put the index on {a:1, b:1}, it very quickly selects small pieces, but when it comes to a large range, I see the error "too much data for sort () without an index". If, otherwise, I put the index on {c:-1, a:-1}, there will be no error, but smaller pieces (and more) are processed much more slowly. So, how can you maintain the speed of choice for smaller ranges, but not get an error with a large amount of data?

If that matters, I run queries through pythongo Python.

Answer 1

, , , MongoDB ( $or), : https://jira.mongodb.org/browse/SERVER-3071.

, , :

- ?

, , {a:1,b:1} , {c:-1,a:-1} find() plus a .

, :

{a:-1,b:1,c:-1}

. $gt $lt , $in, , : http://blog.mongolab.com/2012/06/cardinal-ins/ .

Answer 2

: MongoDB v2.4

- , , , . - , . Mongo ( ) , , .

:

• , . : " sort() ", .find() , , . , A C, .

• . ($lt $gt A), Mongo. MongoDB , . :

  • r = range( 11,100 )
db.mycollection.find({a:{$in: r }, b:4}).sort({c:-1, a:-1})

  • $lt $gt ,
    db.mycollection.find({ a: { $lt:100 }, b:4}).sort({c:-1, a:-1})
    python. , , A=11, !
    , , A B.

$or , $ , $in .

Answer 3

{c: -1, a: -1, b: 1}, .

, , , . , , , , , , , .

. , , . , .

:

> db.createCollection('testIndex')
{ "ok" : 1 }
> db.testIndex.ensureIndex({a:1,b:1})
> db.testIndex.ensureIndex({c:-1,a:-1})
> db.testIndex.ensureIndex({c:-1,a:-1,b:1})
> for(var i=1;i++<500;){db.testIndex.insert({a:i,b:4,c:i+5});}
> for(var i=1;i++<500;){db.testIndex.insert({a:i,b:6,c:i+5});}

te :

> db.testIndex.find({a:{$gt:10,$lt:100}, b:4}).hint('c_-1_a_-1_b_1').sort({c:-1, a:-1}).explain()
{
    "cursor" : "BtreeCursor c_-1_a_-1_b_1",
    "isMultiKey" : false,
    "n" : 89,
    "nscannedObjects" : 89,
    "nscanned" : 588,
    "nscannedObjectsAllPlans" : 89,
    "nscannedAllPlans" : 588,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 1,
    "indexBounds" : {
        "c" : [
            [
                {
                    "$maxElement" : 1
                },
                {
                    "$minElement" : 1
                }
            ]
        ],
        "a" : [
            [
                100,
                10
            ]
        ],
        "b" : [
            [
                4,
                4
            ]
        ]
    },
    "server" :""
}

- , nscanned 588 ( ), nscannedObjects - . mongo , ( ). , scanAndOrder , . ( , , )

, : http://blog.mongolab.com/wp-content/uploads/2012/06/IndexVisitation-4.png, , , , ( ).

Answer 4

, .

db.mycollection.find({b:4, a:{$gt:10,$lt:100}}).sort({c:-1, a:-1})

{b:1,a:-1,c:-1}

Answer 5

,

db.mycollection.ensureIndex({a:1,b:1,c:-1})

{
    "cursor" : "BtreeCursor a_1_b_1_c_-1",
    "nscanned" : 9542,
    "nscannedObjects" : 1,
    "n" : 1,
    "scanAndOrder" : true,
    "millis" : 36,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "isMultiKey" : false,
    "indexOnly" : false,
    "indexBounds" : {
        "a" : [
            [
                3,
                10000
            ]
        ],
        "b" : [
            [
                4,
                4
            ]
        ],
        "c" : [
            [
                {
                    "$maxElement" : 1
                },
                {
                    "$minElement" : 1
                }
            ]
        ]
    }
}

db.mycollection.ensureIndex({b:1,c:-1,a:-1})

> db.mycollection.find({a:{$gt:3,$lt:10000},b:4}).sort({c:-1, a:-1}).explain()
{
    "cursor" : "BtreeCursor b_1_c_-1_a_-1",
    "nscanned" : 1,
    "nscannedObjects" : 1,
    "n" : 1,
    "millis" : 8,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "isMultiKey" : false,
    "indexOnly" : false,
    "indexBounds" : {
        "b" : [
            [
                4,
                4
            ]
        ],
        "c" : [
            [
                {
                    "$maxElement" : 1
                },
                {
                    "$minElement" : 1
                }
            ]
        ],
        "a" : [
            [
                10000,
                3
            ]
        ]
    }
}
> 

I believe that since you are requesting “a” for a range of values ​​and “b” for a specific value, I think the second option is more appropriate. nscanned object changed from 9542 to 1