The Profiler

The Profiler

Finding Slow Queries with the Profiler

explain answers questions about a query you already suspect; the profiler finds the ones you do not know about. Level 1 records operations slower than slowms into a capped system.profile collection in the same database; level 2 records everything and belongs in development only.

Profiling everything slower than 20 ms

db.setProfilingLevel(1, { slowms: 20 });
db.orders.find({ 'items.price': { $gte: 199.96 } }).toArray();   // nothing indexes items.price
db.orders.find({ customerId: 14907 }).toArray();          // indexed: not recorded
db.system.profile.find({}, { _id: 0, millis: 1, planSummary: 1,
  keysExamined: 1, docsExamined: 1, nreturned: 1 }).sort({ millis: -1 }).toArray();
db.setProfilingLevel(0);

Output

[ { keysExamined: 0, docsExamined: 100000, nreturned: 63, millis: 101,
    planSummary: 'COLLSCAN' } ]

The collection scan is caught, the indexed lookup correctly ignored. A profile document also carries command, queryShapeHash, planCacheKey, numYield, execStats and the client's appName: set appName in your connection string (Connection Strings) and every slow query is attributable to a service, and group by queryShapeHash to rank shapes rather than single executions. Profiling is per database and does not survive a restart; in production leave it off and read the log, where mongod writes any operation over slowOpThresholdMs (100 ms by default) as a structured JSON line.

Finding an index nobody uses

$indexStats reports how often the planner chose each index since the server started. One at zero after a full business cycle is dead weight.

Listing indexes the planner never chose

db.orders.aggregate([{ $indexStats: {} },
  { $project: { _id: 0, name: 1, ops: '$accesses.ops', since: '$accesses.since' } },
  { $match: { ops: { $lt: 1 } } }]);

Output

[ { name: 'email_1', ops: Long('0'), since: ISODate('2026-09-22T11:27:46.193Z') },
  { name: 'tags_1', ops: Long('0'), since: ISODate('2026-09-22T11:27:45.793Z') },
  ... 9 more, every index this section only ever reached through explain ... ]

email_1 is the classic case: someone indexed the login field, the application looks customers up by customerId, and the index has cost 905,216 bytes plus a write penalty on every insert since. The elided entries are the counter's one trap: ops counts executed queries only, so an index you have merely explained reads zero. Before dropping one, check since — a counter reset an hour ago proves nothing — and check every replica set member, since secondaries serve different reads. Then hide it, wait, and drop it.

MENU

Finding an index nobody uses