0

I've got a question on how to write an index properly to avoid resorting to a hint.

Sample "Test" Collection Schema

{
   _id: ObjectId(<whatever>),
   a: <whatever>,
   b: <whatever>,
   c: <whatever>,
   d: <whatever>,
   e: {
         f: <whatever>,
         g: <whatever>
      }
}

Index on "Test"

db.test.ensureIndex( { "a": NumberInt(1), "c": NumberInt(1), "_id": NumberInt(1), "d": NumberInt(1) }, 
                     { name: "a_1_c_1__id_1_d_1", background: true } );

Query without hint and query with hint...

> db.test.find({},{d:1}).explain();
{
    "cursor" : "BasicCursor",
    "isMultiKey" : false,
    "n" : 752,
    "nscannedObjects" : 752,
    "nscanned" : 752,
    "nscannedObjectsAllPlans" : 752,
    "nscannedAllPlans" : 752,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 4,
    "nChunkSkips" : 0,
    "millis" : 5,
    "indexBounds" : {

    },
    "server" : <whatever>
}

> db.test.find({},{d:1}).hint("a_1_c_1__id_1_d_1").explain();
{
    "cursor" : "BtreeCursor a_1_c_1__id_1_d_1",
    "isMultiKey" : false,
    "n" : 752,
    "nscannedObjects" : 752,
    "nscanned" : 752,
    "nscannedObjectsAllPlans" : 752,
    "nscannedAllPlans" : 752,
    "scanAndOrder" : false,
    "indexOnly" : true,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
        "a" : [
            [
                {
                    "$minElement" : 1
                },
                {
                    "$maxElement" : 1
                }
            ]
        ],
        "c" : [
            [
                {
                    "$minElement" : 1
                },
                {
                    "$maxElement" : 1
                }
            ]
        ],
        "_id" : [
            [
                {
                    "$minElement" : 1
                },
                {
                    "$maxElement" : 1
                }
            ]
        ],
        "d" : [
            [
                {
                    "$minElement" : 1
                },
                {
                    "$maxElement" : 1
                }
            ]
        ]
    },
    "server" : <whatever>
}

I'd (obviously) like the query to use the covered index but I don't know how to get there without using the hint. Is it possible? I'd prefer to manipulate the index vs. changing the query but changing the query is an option, if need be.

Seanny123
  • 8,776
  • 13
  • 68
  • 124
John Turner
  • 45
  • 2
  • 6
  • If I simplify the index and query to: db.test.ensureIndex( { "d": NumberInt(1) },{ name: "d_1", background: true } ); db.test.find({},{_id:0,d:1}).explain(); I get the same BasicCursor result. – John Turner Sep 13 '13 at 18:11
  • I believe this is something that could be resolve with a JIRA, atm I think MongoDB will ignore if your not putting in a query, I think in my view that should probably change – Sammaye Sep 13 '13 at 18:32

1 Answers1

0

Turns out this is a known issue. Apologies for the post.

https://jira.mongodb.org/browse/SERVER-2109

John Turner
  • 45
  • 2
  • 6
  • Tick and mark it for future visitors, I learned something, I didn't know that JIRA existed, now next time this appears I can direct them – Sammaye Sep 14 '13 at 21:31