Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
    
how do we know it seems that the index is not being used at all. –  Jim Thio Sep 5 '12 at 8:23
    
While it does not tell you which index, this line means that a geo/2d index is being used: "cursor" : "GeoSearchCursor" - as I explained in your other question, geo operators have to use this type of index to function, and this would say BasicCursor if no index was used. –  Adam C Sep 5 '12 at 8:48
    
I see but I used compound index. The second column of the index is indexContents, is it used too? –  Jim Thio Sep 5 '12 at 8:55
    
As I mentioned, you can use hint to test which is faster, then remove the other indexes with a 2d component, leaving only the fastest. This will also save you space and make for more efficient inserts (every index is updated when you insert). Once you only have one 2d index defined, you can then be confident that the one remaining index is in fact the one being used. Unfortunately, because of the bug I linked, with 2 similar indexes, both valid choices, you cannot be 100% sure which is being used unless it is the only 2d index left. –  Adam C Sep 5 '12 at 10:09
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.