Is Index Used for This Query

Question

If so, which one?

How do we know?

I have 3 indexes in a query. Actually only the first 2 should be useful

{
   "v": NumberInt(1),
   "key": {
     "LongitudeLatitude": "2d",
     "Prominent"▼: NumberInt(-1),
     "indexContents": NumberInt(1) 
  },
   "ns": "database.tablebusiness",
   "name": "LongLat_Prominent_indexContents",
   "dropDups": false,
   "background": false 
}

{
   "v": NumberInt(1),
   "key": {
     "LongitudeLatitude": "2d",
     "Prominent": NumberInt(-1) 
  },
   "ns": "database.tablebusiness",
   "name": "LongLat_Prominent",
   "dropDups": false,
   "background": false 
}

This one is not necessary:

{
   "v": NumberInt(1),
   "key": {
     "indexContents": NumberInt(1) 
  },
   "ns": "database.tablebusiness",
   "name": "indexContents",
   "dropDups": false,
   "background": false 
}

Now I do this query

(the query is from PhP so I'll just paste what I see in mongodb log and mongovue

database.tablebusiness query: { LongitudeLatitude: { $nearSphere: [ 106.772835, -6.186753 ], $maxDistance: 0.04498373205078301 }, Prominent: { $gte: 15 }, indexContents: { $all: [ /^aru/, /^op/ ] } } ntoreturn:20 ntoskip:80 nscanned:100 nreturned:20 reslen:1147 1927ms

Now copying { LongitudeLatitude: { $nearSphere: [ 106.772835, -6.186753 ], $maxDistance: 0.04498373205078301 }, Prominent: { $gte: 15 }, indexContents: { $all: [ /^aru/, /^op/ ] } } into mongovue find command gets me the real database command

db.tablebusiness.find({ "LongitudeLatitude" : { "$nearSphere" : [106.772835, -6.186753], "$maxDistance" : 0.044983732050783008 }, "Prominent" : { "$gte" : 15 }, "indexContents" : { "$all" : [/^aru/, /^op/] } }).limit(50);

Which is good enough (except that the limit change a little bit). Doing explain in mongo.exe I got

db.tablebusiness.find({ "LongitudeLatitude" : { "$nearSphere" : [106.772835, -6.186753], "$maxDistance" : 0.044983732050783008 }, "Prominent" : { "$gte" : 15 }, "indexContents" : { "$all" : [/^aru/, /^op/] } }).limit(50).explain();

{
        "cursor" : "GeoSearchCursor",
        "nscanned" : 50,
        "nscannedObjects" : 50,
        "n" : 50,
        "millis" : 3291,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "isMultiKey" : false,
        "indexOnly" : false,
        "indexBounds" : {

        }
}

Now based on that, what index is used? Does mongodb use any index at all? Any tips on how to improve this query will also be appreciated.

Answer 1

The reason you can't tell which one is being used is because of a bug whereby explain does not include the index name of the index being used for geo index queries:

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

What we do know is that it has to be one of the first two you have listed, that have a 2d element first. It cannot be the "indexContents": NumberInt(1) index. You can use .hint() to specify the index to use and then compare the results of the index in each case. I would expect the first one to perform better in general (but be larger) since it contains all of the elements you are querying on:

"LongitudeLatitude": "2d", "Prominent"▼: NumberInt(-1), "indexContents": NumberInt(1)

However, that will depend on the actual data itself. I would test over a representative set of queries and then remove the slower of the two indexes to avoid confusion.