5

Lets say I have document with the following two keys:

1) key1
2) key2

If I am creating compound index on both of them..

{'key1':1,'key2':1}

When running a query relevant only for key1.. does the index above is used? or I need to create specific index only for key1 also?

Thanks

assaf_miz84
  • 687
  • 2
  • 15
  • 33
  • Helpful: http://stackoverflow.com/questions/9901787/how-to-structure-a-compound-index-in-mongodb?rq=1 – Thilo Jul 18 '13 at 22:52
  • 1
    Covered very well in the docs: http://docs.mongodb.org/manual/tutorial/create-indexes-to-support-queries/#create-compound-indexes-to-support-several-different-queries – WiredPrairie Jul 19 '13 at 00:41

2 Answers2

13

In MongoDB, you can use index prefix to query the database. You can't use anything else. If your query does not contain key prefix the index won't be used.

Assuming your proposed index {'key1':1,'key2':1}:

Queries that will use index:

  • db.some.find({key1 : {$gt : 100}}) - uses prefix
  • db.some.find({key1 : {$gt : 100}, key2 : {$lt : 30}}) - uses full index
  • db.some.find({key3 : 'test'}).sort({key1 : 1}) - uses prefix for sort (direction match)

Queries that will NOT use index:

  • db.some.find({key2 : {$gt : 100}}) - index order matters - key2 is not prefix
  • db.some.find({key3 : 'test'}).sort({key1 : -1}) - index direction matters for multicolumn indexes
  • db.some.find({key3 : 'test'}).sort({key2 : 1}) - it's not prefix
Tomáš Fejfar
  • 11,129
  • 8
  • 54
  • 82
  • 2
    This should be the accepted answer, as it more correctly describes the behaviour of [prefix usage](http://docs.mongodb.org/manual/core/index-compound/#compound-index-prefix) for a compound index in MongoDB. – Stennie Sep 18 '15 at 15:17
2

Yes. In a B-tree index, you can use a prefix of the columns.

So you can use the index for a query on 'key1' (but not as efficiently for 'key2', the column order in the index matters).

This is the same situation as in a printed telephone book, which is an index on [lastName, firstName]. You can use that to look up people by lastName easily (and not so easily by firstName, but still more efficient than calling everyone and asking for their first name).

Thilo
  • 257,207
  • 101
  • 511
  • 656
  • 3
    This is not quite correct. An index on (key1, key2) can **not** be used for querying on just key2. key1 needs to be present in your criteria. – Derick Jul 19 '13 at 07:29
  • @Derick: That is maybe a MongoDB limitation. Oracle for example, can do an index-fast-full-scan. I was speaking of B-trees in general (and telephone books). Anyway, the point is that you can only "reasonably" use the index for key1. – Thilo Jul 19 '13 at 07:31
  • 2
    This question is about MongoDB, so your point about Oracle is invalid here. – Derick Jul 21 '13 at 22:16
  • 1
    I was not making a point about Oracle, either. I was speaking of B-trees in general (and telephone books). Sometimes a deeper understanding of these things is beneficial. Anyway, the point is that you can only "reasonably" use the index for key1. – Thilo Jul 22 '13 at 02:07