Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-7638

explain() returns different results than count() when using hint()

    Details

    • Type: Question
    • Status: Closed
    • Priority: Major - P3
    • Resolution: Duplicate
    • Affects Version/s: 2.2.1
    • Fix Version/s: None
    • Component/s: Indexing, Querying
    • Labels:
      None
    • Environment:
      Amazon EC2 running Amazon Linux.

      Description

      Posted to mongo users group also. Looking through the existing bugs I found
      a bug which sounded similar which was that hint() didn't work with count(). There was a vague comment that the results returned when using hint() would not be valid, but nothing more. I'll look up the bug number after this.

      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?

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: