APPENDIX · P9 · MongoDB · INDEXES · FOR SLIDE 7
← Go to Slide 7
P9. MongoDB Indexes: The ESR Rule
// Веб-база: игроки уже подготовлены
db.lab_players.find({ region: "eu", level: { $gte: 20 } },
{ rating: 1, level: 1 }).sort({ rating: -1 })
db.lab_players.createIndex({ region: 1, rating: -1, level: 1 })
db.lab_players.find({ region: "eu", level: { $gte: 20 } },
{ rating: 1, level: 1 }).sort({ rating: -1 })
db.lab_players.createIndex({ tags: 1 })
db.lab_players.find({ tags: "raid" }, { rating: 1 })
db.lab_players.getIndexes()
What the result should be
Before/after the index: same result
on request (4004, then 2001).
Name: region_1_rating_-1_level_1;
tags_1 — multikey for an array.
Web-Run does not support `explain`.
In the local mongosh, compare the explain results:
COLLSCAN/IXSCAN, SORT, nReturned,
totalKeysExamined, totalDocsExamined.
Their values are not guaranteed by ESR.
ESR
explain
Multikey
First, the equality fields (region), then the sorting fields (rating), then the range fields (level).
In the local mongosh instance, check `explain("executionStats")` before and after creating the index
An array index stores a key for each element; in a composite index, the array can consist of a single field.
Indexes are created on demand. Each index slows down writes and consumes memory; keep only those that are used by the `EXPLAIN` command for actual queries.