Re: explain() returns different results than count()

[Barrie]

This conversation can be followed here: https://jira.mongodb.org/browse/SERVER-7638

On Monday, November 12, 2012 8:20:11 PM UTC-5, misko wrote:

I'm attempting to isolate a performance problem with some PHP code and MongoDB.

I'm beginning to think that either hint() or explain() doesn't do what I think it should do.

In the PHP below, I use to not have the hint.  It was painfully slow.  Putting the

hint in made things quicker, but the results don't seem correct.  I'm sure I'm missing

something, but have a read.  Thanks.  MongoDB version 2.2.1.

I'm doing the following in PHP:

    $results = $db->Results->find(array('Test Results' => array('$exists' => true), 'atSchool' => 'yes', 'onNetwork' => 'yes', 'schoolID' => (string)$s['_id']), array('_id' => 1));

    $results = $results->hint(array("Test Results" => 1, "atSchool" => 1,

                                        "onNetwork" => 1, "schoolID" => 1));

    $numR = 0;

    foreach ($results as $r) {

        $numR = $numR + 1;

    }

or in mongo shell:

 db.Results.find( { "Test Results" : { "$exists" : true }, "atSchool" : "yes", "onNetwork" : "yes", "schoolID" : "550627000691" }).hint({"Test Results" : 1, "atSchool" : 1, "onNetwork" : 1, "schoolID" : 1}).explain()

Now here's the issue. explain() returns n=113, which if I read the document correctly should be the number of results returned.

Yet if I run the above mongo shell command with count() instead of explain it returns 5641, which I believe is the correct number.

The PHP code seems to return the same as explain().

explain() output:

{

        "cursor" : "BtreeCursor Test Results_1_atSchool_1_onNetwork_1_schoolID_1",

        "isMultiKey" : false,

        "n" : 113,

        "nscannedObjects" : 177,

        "nscanned" : 6531,

        "nscannedObjectsAllPlans" : 177,

        "nscannedAllPlans" : 6531,

        "scanAndOrder" : false,

        "indexOnly" : false,

        "nYields" : 0,

        "nChunkSkips" : 0,

        "millis" : 50,

        "indexBounds" : {

                "Test Results" : [

                        [

                                {

                                        "$minElement" : 1

                                },

                                {

                                        "$maxElement" : 1

                                }

                        ]

                ],

                "atSchool" : [

                        [

                                "yes",

                                "yes"

                        ]

                ],

                "onNetwork" : [

                        [

                                "yes",

                                "yes"

                        ]

                ],

                "schoolID" : [

                        [

                                "550627000691",

                                "550627000691"

                        ]

                ]

        },

        "server" : "ip-1-1-1-1:27017"

}

count() output:

> db.Results.find( { "Test Results" : { "$exists" : true }, "atSchool" : "yes", "onNetwork" : "yes", "schoolID" : "550627000691" }).hint({"Test Results" : 1, "atSchool" : 1, "onNetwork" : 1, "schoolID" : 1}).count()

5641

If I check the index I have:

[

        {

                "v" : 1,

                "key" : {

                        "Test Results" : 1,

                        "atSchool" : 1,

                        "onNetwork" : 1,

                        "schoolID" : 1

                },

                "ns" : "Edu.Results",

                "name" : "Test Results_1_atSchool_1_onNetwork_1_schoolID_1"

        }

]

I've rebuilt the indexes, run ensureIndex, etc.  No changes.  Anyone have ideas?