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

2dsphere index doesn't work when you put it as third field in ensureIndex()

XMLWordPrintableJSON

    • Type: Icon: Bug Bug
    • Resolution: Done
    • Priority: Icon: Minor - P4 Minor - P4
    • None
    • Affects Version/s: 2.6.7
    • Component/s: Index Maintenance
    • Labels:
      None
    • ALL
    • Hide 
      db.users.ensureIndex({'l':'2dsphere', sex:1, dob:1})
{
	"createdCollectionAutomatically" : false,
	"numIndexesBefore" : 2,
	"numIndexesAfter" : 3,
	"ok" : 1
}

db.users.find({
	l: { $nearSphere: { $geometry: { type: "Point", coordinates: [ 38.911417, 47.257067 ] }, $maxDistance: 600000 } } ,  
	sex: 2, 
	dob: { $gte: new Date(-313657981006), $lte: new Date(1390495618998) }} 
).explain()
{
	"cursor" : "S2NearCursor",
	"isMultiKey" : false,
	"n" : 105,
	"nscannedObjects" : 364,
	"nscanned" : 364,
	"nscannedObjectsAllPlans" : 364,
	"nscannedAllPlans" : 364,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 2,
	"indexBounds" : {
		
	},
	"server" : "airs0urce.local:27017",
	"filterSet" : false
}

      Query time - 2 milliseconds. That's good.
      Then I drop that index and create new one:

      db.users.ensureIndex({sex:1, dob:1, 'l':'2dsphere'})
db.users.find({	
	sex: 2,
	dob: { $gte: new Date(-313657981006), $lte: new Date(1390495618998) },
	l: { $nearSphere: { $geometry: { type: "Point", coordinates: [ 38.911417, 47.257067 ] }, $maxDistance: 600000 } } }
).explain()

{
	"cursor" : "S2NearCursor",
	"isMultiKey" : false,
	"n" : 105,
	"nscannedObjects" : 383926,
	"nscanned" : 383926,
	"nscannedObjectsAllPlans" : 383926,
	"nscannedAllPlans" : 383926,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 1410,
	"indexBounds" : {
		
	},
	"server" : "airs0urce.local:27017",
	"filterSet" : false
}

      Query time - 1410 milliseconds. Also nscanned=383926 (I have 49640 documents in users collection)

      Show
      db.users.ensureIndex({ 'l' : '2dsphere' , sex:1, dob:1}) { "createdCollectionAutomatically" : false , "numIndexesBefore" : 2, "numIndexesAfter" : 3, "ok" : 1 } db.users.find({ l: { $nearSphere: { $geometry: { type: "Point" , coordinates: [ 38.911417, 47.257067 ] }, $maxDistance: 600000 } } , sex: 2, dob: { $gte: new Date(-313657981006), $lte: new Date(1390495618998) }} ).explain() { "cursor" : "S2NearCursor" , "isMultiKey" : false , "n" : 105, "nscannedObjects" : 364, "nscanned" : 364, "nscannedObjectsAllPlans" : 364, "nscannedAllPlans" : 364, "scanAndOrder" : false , "indexOnly" : false , "nYields" : 0, "nChunkSkips" : 0, "millis" : 2, "indexBounds" : { }, "server" : "airs0urce.local:27017" , "filterSet" : false } Query time - 2 milliseconds. That's good. Then I drop that index and create new one: db.users.ensureIndex({sex:1, dob:1, 'l' : '2dsphere' }) db.users.find({ sex: 2, dob: { $gte: new Date(-313657981006), $lte: new Date(1390495618998) }, l: { $nearSphere: { $geometry: { type: "Point" , coordinates: [ 38.911417, 47.257067 ] }, $maxDistance: 600000 } } } ).explain() { "cursor" : "S2NearCursor" , "isMultiKey" : false , "n" : 105, "nscannedObjects" : 383926, "nscanned" : 383926, "nscannedObjectsAllPlans" : 383926, "nscannedAllPlans" : 383926, "scanAndOrder" : false , "indexOnly" : false , "nYields" : 0, "nChunkSkips" : 0, "millis" : 1410, "indexBounds" : { }, "server" : "airs0urce.local:27017" , "filterSet" : false } Query time - 1410 milliseconds. Also nscanned=383926 (I have 49640 documents in users collection)

      I have "users" collection with these fields:
      dob - ISODate
      sex - Int
      l - geoJSON

      I want index for all the fields.

      http://docs.mongodb.org/manual/tutorial/build-a-2dsphere-index/
      From docs I see, that: "Unlike the 2d index, a compound 2dsphere index does not require the location field to be the first field indexed."

      So, it means I can create indexes different ways

      Works in queries:

       db.users.ensureIndex({'l':'2dsphere', sex:1, dob:1})

      Works in queries:

      db.users.ensureIndex({sex:1, 'l':'2dsphere', dob:1})

      In this case index will not be used in queries

      db.users.ensureIndex({sex:1, dob:1, 'l':'2dsphere'})

      Not sure if this is bug. But if not then it makes sense to change docs and inform
      developers that location field must be first or second field in index.

            Assignee:
            rassi J Rassi
            Reporter:
            airs0urce airs0urce
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

              Created:
              Updated:
              Resolved: