Skip to main content

Compound indexes

In MongoDB, a compound index is an index that involves multiple fields within a collection's documents. Compound indexes can greatly improve query performance by allowing the database engine to filter on multiple criteria simultaneously. They are particularly useful for queries that involve sorting or filtering on more than one field.

Creating a Compound Index​

To create a compound index, you can use the createIndex method and specify multiple fields. For example, to create a compound index on the username and email fields of a users collection, you would do:

db.users.createIndex({ "username": 1, "email": 1 })

Here, 1 indicates that the index for each field is in ascending order. You can also mix ascending (1) and descending (-1) orders for different fields.

Querying with Compound Indexes​

Once a compound index is created, MongoDB can use it for queries that involve any of the fields in the index, as well as combinations of those fields. For example:

// Query using both fields
db.users.find({ "username": "john_doe", "email": "john@example.com" })

// Query using one field
db.users.find({ "username": "john_doe" })

Both of these queries would benefit from the compound index on username and email.

Sort Operations​

Compound indexes can also be used to optimize sort operations:

// Sort by username and then by email
db.users.find().sort({ "username": 1, "email": 1 })

Prefixes and Index Use​

A compound index on fields (a, b, c) can also support queries that involve just a, or a and b, but not just b or c. In other words, you can use the "prefix" of a compound index to optimize queries.

Considerations​

  1. Index Size: Compound indexes are generally larger than single-field indexes, so they consume more disk space and memory.

  2. Query Complexity: While compound indexes can speed up complex queries, they add some overhead for write operations, as each write must update the index.

  3. Order Matters: The order of fields in a compound index is important. It affects which types of queries can be optimized.

  4. Covered Queries: A compound index can make a query "covered" if all the fields involved in the query and the fields to be returned are in the index.

  5. Limitations: MongoDB imposes a limit on the number of fields in a compound index (currently 32).