2

From MongoDB Documentation

If you have a compound index on multiple fields, you can use it to query on the beginning subset of fields. So if you have an index on a,b,c you can use it query on [a] [a,b] [a,b,c]

So lets say i have document with this fields

  1. UserID
  2. Name
  3. Country
  4. ExtraField

My Index order is [UserID,Name,Country]

So if i have query like

  var q = (from c in collection.AsQueryable()
                 where c.UserID == UserID
                 where Name = "test"
                 where Country = 1
                 where ExtraField = "check"

                 select c);

Does this query use index for first 3 parameters and then search for ExtraField without index?

If yes, then is it same on this query too

 var q = (from c in collection.AsQueryable()
                 where c.UserID == UserID                   
                 where ExtraField = "check"

                 select c);
Community
  • 1
  • 1
Novkovski Stevo Bato
  • 1,013
  • 1
  • 23
  • 56
  • 1
    It should be able to use index in these two queries. I don't use C# driver, can you view explain plans there (or can you see in application logs exactly what query is sent to the database)? – Sergio Tulentsev Sep 05 '12 at 10:13

1 Answers1

3

The answer to both questions is yes.

For your first query, the performance will depend on the selectivity of the result set. So if the 3 fields in the index matched a very large number of documents, performance would be slow as all those documents would need to be scanned to match on ExtraField. If only a few documents were matched, performance would be fast.

Now, if your query didn't include the first field in the index at all, the index would not be used. The following query, for example, would not be able to use the index:

var q = (from c in collection.AsQueryable()
              where Name = "test"
              where Country = 1
              select c);

Have a look here for some interesting facts about finding other combinations of fields in the index.

I would recommend using the explain command when in doubt about questions like this.

Community
  • 1
  • 1
Zaid Masud
  • 13,225
  • 9
  • 67
  • 88
  • For first query, my assumption for all docs by UserID are this numbers (60% between 0-400, 20% between 401-1000, 15% between 1001-2000, 5% more). From your experience, is that slow? – Novkovski Stevo Bato Sep 05 '12 at 15:13
  • @NovkovskiStevoBato No I don't think that will be too slow. Have a look at nscanned in your explain command, you want this to be as small as possible. You don't want nscanned to be in tens of thousands. If it is occasionally in the thousands it will be somewhat slow but might still be acceptable. If it's in the hundreds it will be *very fast*. – Zaid Masud Sep 05 '12 at 15:54