Index intersection behavior in MongoDB

Introduction

Index intersection helps MongoDB use multiple indexes together to find data faster. It combines indexes to answer queries efficiently.

When a query filters on multiple fields that each have their own index.

When no single index covers all query conditions but multiple indexes exist.

When you want to speed up searches without creating a big combined index.

When your data has many different query patterns on separate fields.

Syntax

MongoDB

No special syntax is needed. MongoDB automatically uses index intersection when multiple indexes can help a query.

MongoDB decides internally when to use index intersection based on query and indexes.

You can check if index intersection is used by running explain() on your query.

Examples

Two separate indexes exist on field1 and field2. MongoDB may use index intersection to combine them for the query.

MongoDB

db.collection.createIndex({ field1: 1 })
db.collection.createIndex({ field2: 1 })
db.collection.find({ field1: 'value1', field2: 'value2' })

This shows if MongoDB used index intersection by looking at the query plan details.

MongoDB

db.collection.find({ field1: 'value1', field2: 'value2' }).explain('executionStats')

Sample Program

This example creates two indexes on 'category' and 'price'. Then it inserts some products. The find query filters on both fields. MongoDB may use index intersection to speed up this query. The explain output shows the query plan.

MongoDB

db.products.createIndex({ category: 1 })
db.products.createIndex({ price: 1 })
db.products.insertMany([
  { name: 'Pen', category: 'Stationery', price: 5 },
  { name: 'Notebook', category: 'Stationery', price: 15 },
  { name: 'Mug', category: 'Kitchen', price: 10 },
  { name: 'Plate', category: 'Kitchen', price: 20 }
])
db.products.find({ category: 'Stationery', price: { $lt: 20 } }).explain('executionStats')

Important Notes

Index intersection can improve query speed but sometimes a single combined index is faster.

Use explain() to see if index intersection is used and how effective it is.

Creating too many indexes can slow down writes, so balance your indexes wisely.

Summary

Index intersection lets MongoDB combine multiple indexes to answer queries.

No special syntax is needed; MongoDB decides automatically.

Use explain() to check if index intersection is helping your queries.