Identifying missing indexes in MongoDB

Introduction

Indexes help MongoDB find data faster. Identifying missing indexes shows where queries can be improved to run quicker.

When queries on a collection are slow and you want to speed them up.

When you notice high CPU or disk usage during database operations.

When MongoDB suggests creating indexes in its logs or profiler.

When you want to optimize reports or dashboards that take too long to load.

Syntax

MongoDB

db.collection.getIndexes()
db.collection.find().explain('queryPlanner')
use system.profile collection to check slow queries

Use db.collection.getIndexes() to see existing indexes on a collection.

Use explain() on queries to see if indexes are used or missing.

Examples

Shows all indexes on the 'orders' collection.

MongoDB

db.orders.getIndexes()

Shows how MongoDB runs this query and if it uses an index.

MongoDB

db.orders.find({customerId: 123}).explain('executionStats')

Finds recent slow queries taking more than 100 milliseconds.

MongoDB

db.system.profile.find({millis: {$gt: 100}}).sort({ts: -1}).limit(5)

Sample Program

This script checks current indexes, explains a query to see if it uses an index, and lists recent slow queries to identify missing indexes.

MongoDB

use shopDB

// Check existing indexes on products collection
printjson(db.products.getIndexes())

// Run a query and explain to see if index is used
var explainResult = db.products.find({category: 'books'}).explain('executionStats')
printjson(explainResult.queryPlanner)

// Check slow queries from profiler
var slowQueries = db.system.profile.find({millis: {$gt: 50}}).sort({ts: -1}).limit(3).toArray()
printjson(slowQueries)

Important Notes

If a query uses COLLSCAN (collection scan), it means no index was used and performance can be slow.

The system.profile collection stores recent slow queries if profiling is enabled.

Creating indexes on fields used in queries can greatly improve speed.

Summary

Indexes help MongoDB find data faster and reduce query time.

Use getIndexes() and explain() to check if indexes exist and are used.

Look at slow queries in system.profile to find where indexes are missing.