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

Having compound indexes scans less objects during a query but takes more time than a single index

    XMLWordPrintableJSON

Details

    • Icon: Question Question
    • Resolution: Done
    • Icon: Major - P3 Major - P3
    • None
    • 2.0.2
    • Querying
    • MacBook Pro 2010 running Mac OS X Snow Leopard

    Description

      I am storing documents with 2 fields: "date" and "b". For "date" I want to check that it is greater than or less than a certain date. For "b" I want to know whether or not it exists.

      I want to query on both fields simultaneously.

      I created some test data as follows:

      date = new ISODate();

      for (var i=0; i < 5000000; i++) {
      m = Math.floor(Math.random()*12);
      d = Math.floor(1+Math.random()*28);
      date.setMonth(m);
      date.setDate(d);
      db.bar.insert(

      {"date":date}

      );
      }
      for (var i=0; i < 5000000; i++) {
      m = Math.floor(Math.random()*12);
      d = Math.floor(1+Math.random()*28);
      date.setMonth(m);
      date.setDate(d);
      b = (Math.random() > 0.5);
      db.bar.insert(

      {"date":date,"b":b}

      );
      }

      Then I run 2 tests.

      Test 1) I put an index on date only, and do a query explain.

      db.bar.ensureIndex(

      {"date":1}

      );
      db.bar.find({"date":{$lte:date},"b":{$exists:false}}).explain();

      Test 2) I put a compound index on date and b, and do a query explain.

      db.bar.ensureIndex(

      {"date":1,"b":1}

      );
      db.bar.find({"date":{$lte:date},"b":{$exists:false}}).explain();

      Results of the tests: Having a compound index on "date" and "b" runs a bit slower, even though it scans less documents. Here is the output of the explain()s:

      Test 1)

      {
      "cursor" : "BtreeCursor date_1",
      "nscanned" : 6128089,
      "nscannedObjects" : 6128089,
      "n" : 3775151,
      "millis" : 17420,
      "nYields" : 0,
      "nChunkSkips" : 0,
      "isMultiKey" : false,
      "indexOnly" : false,
      "indexBounds" :

      { "date" : [ [ true, ISODate("2012-06-18T22:06:03.419Z") ] ] }

      }

      Test 2)

      {
      "cursor" : "BtreeCursor date_1_b_1",
      "nscanned" : 3775309,
      "nscannedObjects" : 3775151,
      "n" : 3775151,
      "millis" : 18100,
      "nYields" : 0,
      "nChunkSkips" : 0,
      "isMultiKey" : false,
      "indexOnly" : false,
      "indexBounds" :

      { "date" : [ [ true, ISODate("2012-06-18T22:06:03.419Z") ] ], "b" : [ [ null, null ] ] }

      }

      Attachments

        Activity

          People

            aaron Aaron Staple
            jc Justin
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: