Index Usage Questions: does find({ field_a}.sort({field_b} use index (fielda, fieldb) ?

[Kevin Rice]

Hello!

Question re: index usage - I may have extraneous indexes if Mongo isn't using them, thus a slowdown on insert/update, thus the question:

My sharded, replicated collection has uniform records with fields FieldA and FieldB.  There are two indexes on this collection:  

• IndexOne has key FieldA only.
• IndexTwo has keys FieldA and FieldB.

Questions:

1. On a db.collectionName.find({ 'FieldA' : 999}), I would figure Index IndexOne would be used.
2. If IndexOne didn't exist and I did db.collectionName.find({ 'FieldA' : 999}), I would figure Index IndexTwo would be used.
3. If I do db.collectionName.find({'FieldA':999}).sort({'FieldB': 1})  (plus or minus syntax here), which index would be used? 
4. If IndexOne doesn't exist, can the find().sort() call use IndexTwo?
5. In Postgres, I can do an 'analyze explain <queryOne>' that shows me what index it uses for queryOne.  How do I do this in Mongo?
   

[Sam Martin]

On Thursday, October 11, 2012 4:39:21 PM UTC+1, Kevin Rice wrote:
> Hello!


db.collectionName.find({'FieldA':999}).sort({'FieldB': 1})  should use indextwo

If you
db.collectionName.find({'FieldA':999}).sort({'FieldB': 1}) .explain()
you will see which index is being used if any.

> Question re: index usage - I may have extraneous indexes if Mongo isn't using them, thus a slowdown on insert/update, thus the question:
>
> My sharded, replicated collection has uniform records with fields FieldA and FieldB.  There are two indexes on this collection:  
> IndexOne has key FieldA only.IndexTwo has keys FieldA and FieldB.Questions:

> On a db.collectionName.find({ 'FieldA' : 999}), I would figure Index IndexOne would be used.If IndexOne didn't exist and I did db.collectionName.find({ 'FieldA' : 999}), I would figure Index IndexTwo would be used.If I do db.collectionName.find({'FieldA':999}).sort({'FieldB': 1})  (plus or minus syntax here), which index would be used?  If IndexOne doesn't exist, can the find().sort() call use IndexTwo?In Postgres, I can do an 'analyze explain <queryOne>' that shows me what index it uses for queryOne.  How do I do this in Mongo?

[Sam Helman]

[Sam Helman]

Sorry about the blank message, here are your answers - as Sam Martin described, using explain() http://www.mongodb.org/display/DOCS/Explain can show you what index is used for a query.

1. Correct!

2. Correct!

3. IndexTwo, as determined by an explain() on the query.

4. Correct!

5. See explain()