Unit 5 — Advanced Query Processing and Optimization

Topics Covered

Syllabus topics: Query optimization and operations — projection, limiting and skipping records, sorting records. Indexing in MongoDB (single field, compound, multikey, text index). Aggregation framework (pipelines, stages, operators). Replication concepts — replica sets, failover, consistency.

5.1 Projection, sorting, limiting and skipping

These were introduced in Unit 3 §§3.12–3.13; here they matter for performance.

db.students.find({ dept: "DS" }, { name: 1, "marks.maths": 1, _id: 0 })
          .sort({ "marks.maths": -1 })
          .skip(0)
          .limit(10)

The server always applies sort, then skip, then limit — regardless of the order you chain them. So .limit(3).sort(...) sorts the entire result and then takes three; it does not sort three arbitrary documents.

⚠️ An unindexed sort fails outright past 100 MB

db.students.find().sort({ score: -1 })
// QueryExceededMemoryLimitNoDiskUseAllowed, if the result exceeds 100 MB

An in-memory sort is capped at 100 MB. The fixes, in order of preference: create an index on the sort key (then the sort is free, because the index is already ordered); or pass allowDiskUse: true and accept that it will be slow.

An indexed sort also lets limit stop early: the server reads ten index entries and stops, instead of sorting a million documents to discard all but ten.

⚠️ skip does not scale

skip(100000) makes the server walk and throw away 100,000 documents. Page 500 of a listing is genuinely, linearly slower than page 2.

Range pagination uses the index to jump straight there:

// page 1
db.students.find().sort({ _id: 1 }).limit(20)
// next page: remember the last _id you saw
db.students.find({ _id: { $gt: lastSeenId } }).sort({ _id: 1 }).limit(20)

Every page then costs the same. The trade is that you cannot jump to an arbitrary page number — which is why infinite-scroll interfaces are common and "page 500" links are not.

5.2 Why indexes

db.students.createIndex({ dept: 1 })              // 1 ascending, -1 descending
db.students.createIndex({ dept: 1, "marks.maths": -1 })   // compound
db.students.createIndex({ email: 1 }, { unique: true })
db.students.createIndex({ name: "text" })
db.students.createIndex({ dept: 1 }, { name: "dept_idx" })
db.students.createIndex({ createdAt: 1 }, { expireAfterSeconds: 3600 })  // TTL
db.students.createIndex({ email: 1 },
  { unique: true, partialFilterExpression: { email: { $exists: true } } })

db.students.getIndexes()
db.students.dropIndex("dept_1")
db.students.totalIndexSize()

⚠️ Indexes are not free

The rule: index what you query, not everything. A collection with fifteen indexes on a write-heavy workload can be slower overall than one with three. The limit is 64 per collection, and reaching it means something is wrong with the design.

5.3 Index types

db.students.createIndex({ dept: 1 })

Compound

db.students.createIndex({ dept: 1, "marks.maths": -1 })

Covered in detail in §5.4 — the prefix rule is the examinable part.

db.students.createIndex({ subjects: 1 })

db.articles.createIndex({ title: "text", body: "text" })
db.articles.find({ $text: { $search: "mongodb aggregation" } })
db.articles.find({ $text: { $search: "\"exact phrase\"" } })
db.articles.find({ $text: { $search: "mongodb -mysql" } })      // - EXCLUDES

db.articles.find({ $text: { $search: "mongodb" } },
                 { score: { $meta: "textScore" } })
          .sort({ score: { $meta: "textScore" } })

db.articles.createIndex({ title: "text", body: "text" },
                        { weights: { title: 10, body: 1 } })

The others

⚠️ Unique indexes and missing fields

db.students.createIndex({ email: 1 }, { unique: true })
db.students.insertOne({ name: "A" })          // ok -- email is missing (null)
db.students.insertOne({ name: "B" })          // DUPLICATE KEY ERROR

A missing field indexes as null, and two nulls collide. So a unique index allows one document without the field, then rejects every other. The fix is a partial index, which excludes them from the index entirely:

db.students.createIndex({ email: 1 },
  { unique: true, partialFilterExpression: { email: { $exists: true } } })

(A sparse: true unique index does the same job and is the older idiom; partial indexes are more general and are now preferred.)

5.4 Compound indexes and the prefix rule

// Query: dept = "DS", maths > 70, sorted by age
db.students.createIndex({ dept: 1, age: 1, "marks.maths": 1 })
//                        ^equality  ^sort   ^range

Direction, in a compound index

db.students.createIndex({ dept: 1, marks: -1 })

serves sort({ dept: 1, marks: -1 }) and its exact reverse sort({ dept: -1, marks: 1 }) — because walking the index backwards produces the opposite of every field. It does not serve sort({ dept: 1, marks: 1 }), because that is neither the index order nor its reverse.

5.5 explain()

db.students.find({ dept: "DS" }).explain("executionStats")

1     ideal -- every document examined was returned
10    acceptable
1000  the index is wrong, or missing

db.students.createIndex({ dept: 1, name: 1 })
db.students.find({ dept: "DS" }, { _id: 0, dept: 1, name: 1 })

5.6 Query optimization in practice

⚠️ Operators that cannot use an index

$ne is the surprising one. An index makes "find x" fast by jumping to a contiguous range; "find everything that is not x" is the entire index minus one range, so scanning it is no better than scanning the collection.

5.7 Replication

                 ┌───────────┐
   writes ──────►│  PRIMARY  │
                 └─────┬─────┘
                       │ oplog
            ┌──────────┴──────────┐
            ▼                     ▼
     ┌─────────────┐       ┌─────────────┐
     │ SECONDARY 1 │       │ SECONDARY 2 │
     └─────────────┘       └─────────────┘

⚠️ Replication is not backup

A replica set copies every operation, including deleteMany({}). A mistaken deletion propagates to all three members within milliseconds. Replication protects against hardware failure, not against mistakes — you still need backups and, ideally, a delayed member that lags by an hour.

The oplog

The oplog (operations log) is a capped collection on the primary recording every write. Secondaries tail it and replay the operations.

Its operations are idempotent — $inc: {n: 1} is rewritten as $set: {n: 5} — so replaying one twice is harmless, which is what makes recovery after a crash safe.

Oplog size is what limits how long a secondary can be offline. If a secondary is down longer than the oplog's window, the operations it missed have been overwritten and it must resynchronise from scratch.

1. The primary stops responding to heartbeats (default: 10 seconds).
2. A secondary calls an election.
3. Members vote. A candidate needs a MAJORITY of votes.
4. The winner becomes primary; the others follow it.
5. The old primary, on returning, becomes a SECONDARY and rolls back
   any writes that were never replicated.

⚠️ Why the number of members should be odd

A majority is required to elect a primary. With an even number, a network partition can split the set into two equal halves, neither of which has a majority, so no primary is elected and the whole set becomes read-only.

Four members survive no more failures than three. That is why the recommendation is three or five, and why an arbiter exists — a member that votes but holds no data, used to make an even set odd cheaply.

Member types

db.students.insertOne(doc, { writeConcern: { w: "majority", j: true, wtimeout: 5000 } })

Read preference and read concern

db.students.find().readPref("secondaryPreferred")
db.students.find().readConcern("majority")

Reading from a secondary trades consistency for throughput. A secondary may be milliseconds or seconds behind, so secondaryPreferred can return stale data. That is the AP end of the tunable spectrum, and it is fine for a dashboard and wrong for a balance check.

Setting one up

rs.initiate({
  _id: "rs0",
  members: [
    { _id: 0, host: "mongo1:27017" },
    { _id: 1, host: "mongo2:27017" },
    { _id: 2, host: "mongo3:27017" }
  ]
})

rs.status()          rs.conf()          rs.isMaster()
rs.add("mongo4:27017")                  rs.remove("mongo4:27017")
rs.stepDown()                           // force a failover, for testing
rs.printSecondaryReplicationInfo()      // replication LAG per secondary

5.8 Sharding, briefly

Replication copies the same data to every member; sharding splits different data across machines. They are complementary, and each production shard is itself a replica set.

        mongos (router)
       /      |       \
  Shard A   Shard B   Shard C          ← each a replica set
  (a–h)     (i–p)     (q–z)

The shard key is the decision that matters, and it is very hard to change later. It should have high cardinality, even distribution and match your queries. A monotonically increasing key such as a timestamp is the classic mistake: every new document goes to the same shard, so writes do not scale at all. A hashed shard key fixes the distribution and gives up range queries.

5.9 Transactions and GridFS

Multi-document transactions

const session = db.getMongo().startSession()
session.startTransaction()
try {
  const accounts = session.getDatabase("bank").accounts
  accounts.updateOne({ _id: "A" }, { $inc: { balance: -500 } })
  accounts.updateOne({ _id: "B" }, { $inc: { balance:  500 } })
  session.commitTransaction()
} catch (e) {
  session.abortTransaction()
  throw e
} finally {
  session.endSession()
}

A single-document write has always been atomic. Transactions add multi-document ACID guarantees, available since 4.0 for replica sets and 4.2 across shards.

They are not free, and knowing that is the mark: transactions hold locks, have a default 60-second limit, and are slower than a single-document write. A schema that needs transactions for its common operations is usually a schema that should have embedded. The transfer above genuinely needs one; a student and their address do not.

GridFS

The 16 MB document limit means a large file cannot be a document. GridFS splits it into 255 KB chunks across two collections:

mongofiles -d mydb put video.mp4
mongofiles -d mydb get video.mp4
mongofiles -d mydb list

Use GridFS when a file exceeds 16 MB, or when you need to read ranges of it — seeking to the middle of a video means fetching a few chunks, not the whole file. For smaller files, or where a CDN is available, object storage such as S3 is usually the better answer; GridFS's advantage is that the files live in the database, replicated and backed up with everything else.

Practice problems

db.students.find({ dept: "DS" })
db.students.find({ dept: "DS", year: 4 })
db.students.find({ dept: "DS" }).sort({ cgpa: -1 }).limit(10)
db.students.find({ year: 4 })

db.students.createIndex({ dept: 1, cgpa: -1 })

db.students.createIndex({ dept: 1, year: 1 })

db.students.createIndex({ year: 1 })

db.students.find({ year: 4 }).explain("executionStats")
// stage: IXSCAN (not COLLSCAN)
// totalDocsExamined / nReturned should be close to 1

db.orders.find({ status: "shipped", total: { $gt: 1000 } })
         .sort({ orderDate: -1 })
         .limit(20)

db.orders.find({ status: "shipped", total: { $gt: 1000 } })
         .sort({ orderDate: -1 }).limit(20)
         .explain("executionStats")

db.orders.createIndex({ status: 1, orderDate: -1, total: 1 })

stage: IXSCAN
SORT stage: ABSENT          ← the index provided the order
totalDocsExamined: ~25
nReturned: 20
ratio: ~1.25

db.orders.createIndex({ status: 1, orderDate: -1, total: 1, customer: 1 })
db.orders.find({ status: "shipped", total: { $gt: 1000 } },
               { _id: 0, orderDate: 1, total: 1, customer: 1 })
         .sort({ orderDate: -1 }).limit(20)
// totalDocsExamined: 0 -- the documents are never read at all

Exam questions from this unit

Two marks

1. What is an index, and what does it cost?
2. Distinguish COLLSCAN from IXSCAN.
3. What is a multikey index?
4. How many text indexes may a collection have?
5. State the ESR rule.
6. State the index prefix rule.
7. What is a covered query?
8. Why does an unindexed sort fail past 100 MB?
9. Why must a replica set have an odd number of members?
10. What is the oplog?
11. Distinguish w: 1 from w: "majority".
12. Why is replication not a backup?
13. What is GridFS, and when is it needed?

Five marks

1. Explain indexing in MongoDB with all the index types.
2. Explain compound indexes, the prefix rule and ESR.
3. Explain explain() and what to read from it.
4. Explain query optimization techniques.
5. Explain replica sets, elections and failover.
6. Explain write concern and read preference.
7. Explain sharding and the choice of shard key.
8. Explain transactions and when they should be avoided.

Ten marks

1. Given a collection and its queries, design the indexes and justify each.

2. Explain replication exhaustively — members, oplog, elections, failover, write concern and read preference.

3. Diagnose and optimise a slow query, step by step.

Mistakes that cost marks