What is the ESR rule?

Definition:

ESR stands for Equality, Sort and Range, a common starting point for ordering fields in a compound index. Equality fields narrow the relevant index region, sort fields can let MongoDB read in the desired order, and range fields constrain intervals. It is a guideline that must be checked against selectivity and the query plan.

Key points:

• Equality → Sort → Range is a starting point for compound index order.

• Example: filter by status, sort by createdAt, then apply a range field.

• Confirm with explain; highly selective range filters may justify another order.

• A compound index is ordered, so a query that skips leading fields may use it less effectively.

Example — Mongo shell:

db.orders.createIndex({ status: 1, createdAt: -1 });
db.orders.find({ status: 'paid' }).sort({ createdAt: -1 }).limit(20);

• status is the equality field and createdAt supports the requested order. Confirm the plan for the actual dataset. MongoDB ESR guidance.

Interview point:

• Explain why the field order serves a specific filter-and-sort query, then verify with execution statistics.