0

Let's say I have a collection with 20 fields and a query that uses 5 of them, to find the documents I need.

3/5 of those fields are always used in the query, 1 for equality comparison, 1 for range comparison (less than) and the other for sorting. The other 2 are being used conditionally, so sometimes they will and others they won't.

Those 2, should I index them? If I don't, will I still benefit from indexing the first 3?

0

Let's say I have a collection with 20 fields and a query that uses 5 of them, to find the documents I need.

3/5 of those fields are always used in the query, 1 for equality comparison, 1 for range comparison (less than) and the other for sorting. The other 2 are being used conditionally, so sometimes they will and others they won't.

Those 2, should I index them?

If you have an index on three fields supporting your common equality/sort/range queries, this index is still a viable candidate for a query including additional fields. There will be some performance cost for fetching documents to find values that aren't included in the index, but this may be acceptable depending on your queries and data distribution.

The details of your common queries will also influence whether an index on five fields can effectively support lookups on three. If your common queries include different combinations of equality, range, and sort fields you will likely need multiple indexes.

One situation to be wary of is having multiple candidate indexes that might support a given query shape, but no ideal index. For example: if you have an index on the three common fields and a separate index including the two less common fields, the outcome of planning for a query involving all five fields may not be deterministic.

If I don't, will I still benefit from indexing the first 3?

If you don't create an index on any of the fields used in a query, the only possible query plan will be a collection scan (i.e. read all documents in the collection to find matching values). That's generally a situation to avoid unless your query is likely to fetch most of a collection. An index on a subset of fields is likely to be more useful than no index.

The overall benefits can only be qualified by comparing the execution stats given your actual queries and representative data. An index on all fields might be more efficient if the additional fields are selective and there would be relatively few index lookups compared to the number of documents to scan. However, additional fields will also increase the size of the index data which might be detrimental if these fields aren't frequently queried.

For a better comparison I would try creating both indexes in a test environment with some representative data and then review the explain results for candidate plans for your common queries:

db.mycollection.find(...).explain('allPlansExecution')

If you have many candidate indexes you may find it easier to use an index hint to force a specific index to be selected in your test environment. For production environments you should only add required indexes.

Another consideration would be matching indexes to actual usage in your deployment. You could add indexes based on slow or inefficient queries as observed in your deployment (or potentially identify unused indexes to remove). For a start on log-based analysis, see Understanding IXSCAN and COLLSCAN in MongoDB logs on Server Fault.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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