{
  "$type": "site.standard.document",
  "bskyPostRef": {
    "cid": "bafyreifhkeiyoydj22vh3qz3diwdxqo5oozvfjzw6jddykdmcrqtbgttwy",
    "uri": "at://did:plc:25rdn5elo5izoxrmtis34zuk/app.bsky.feed.post/3mozn6dn5vbp2"
  },
  "coverImage": {
    "$type": "blob",
    "ref": {
      "$link": "bafkreibesuaxnyccyhuw24goxof27dohkmshjsttyhgmfn2q5qzy2axgbm"
    },
    "mimeType": "image/webp",
    "size": 61830
  },
  "path": "/aarthirs/mongodb-indexes-finally-clicked-for-me-understanding-indexes-compound-indexes-the-prefix-rule-1f45",
  "publishedAt": "2026-06-24T09:34:53.000Z",
  "site": "https://dev.to",
  "tags": [
    "webdev",
    "mongodb",
    "backend",
    "database"
  ],
  "textContent": "While working on a MERN project, I came across these indexes:\n\n\n\n    transactionSchema.index({ user: 1, date: -1 });\n    transactionSchema.index({ user: 1, type: -1 });\n    transactionSchema.index({ user: 1, category: -1 });\n\n\nMy first reaction was:\n\n> \"Why are we creating 3 different indexes for the same schema? Isn't one index enough?\"\n\nAt that time, my understanding was:\n\n> \"Indexes help MongoDB find records faster.\"\n\nWhich is true, but it wasn't enough to explain why multiple indexes existed for the same collection.\n\nThat simple doubt led me down a rabbit hole of learning about indexes, compound indexes, how MongoDB stores them, and the famous Prefix Rule.\n\nHere's what I learned.\n\n#  What is an Index?\n\nImagine a collection with millions of transactions.\n\n\n\n    db.transactions.find({\n      user: \"Aarthi\"\n    });\n\n\nWithout an index, MongoDB may need to inspect every document until it finds the matching records.\n\nThis is called a **Collection Scan**.\n\nThink of it like searching for a chapter in a book without a table of contents. You'd have to flip through page after page until you find it.\n\nAn index works like a book's table of contents.\n\nInstead of scanning every document, MongoDB can jump directly to the relevant records.\n\nExample:\n\n\n\n    db.transactions.createIndex({\n      user: 1\n    });\n\n\nNow MongoDB can quickly locate all transactions belonging to a specific user.\n\n#  What is a Compound Index?\n\nA compound index contains multiple fields.\n\nExample:\n\n\n\n    db.transactions.createIndex({\n      user: 1,\n      date: -1\n    });\n\n\nThis means MongoDB organizes the index by:\n\n\n\n    user\n      └── date\n\n\nConceptually, it looks something like:\n\n\n\n    Aarthi\n       2025-08-10\n       2025-08-09\n       2025-08-08\n\n    John\n       2025-08-10\n       2025-08-05\n\n\nThe data is first grouped by `user`, and within each user, it is ordered by `date`.\n\nNow queries like:\n\n\n\n    db.transactions.find({\n      user: \"Aarthi\"\n    }).sort({\n      date: -1\n    });\n\n\nbecome very efficient.\n\nMongoDB can jump directly to Aarthi's records and retrieve them in date order.\n\n#  The Prefix Rule: The Concept That Finally Made It Click\n\nConsider this index:\n\n\n\n    {\n      user: 1,\n      date: -1\n    }\n\n\nMongoDB can efficiently use it for:\n\n\n\n    find({\n      user: \"Aarthi\"\n    });\n\n\n✅ Works\n\n\n\n    find({\n      user: \"Aarthi\",\n      date: \"2025-08-10\"\n    });\n\n\n✅ Works\n\nBut:\n\n\n\n    find({\n      date: \"2025-08-10\"\n    });\n\n\n❌ Not efficient\n\nWhy?\n\nBecause the index is organized by `user` first and then by `date`.\n\nMongoDB knows where each user's records start, but it doesn't know where a specific date begins without first navigating through the user groups.\n\nThis behavior is known as the **Prefix Rule**.\n\nA compound index can efficiently support queries that start from the leftmost fields of the index.\n\nFor example:\n\n\n\n    {\n      user: 1,\n      date: -1,\n      type: 1\n    }\n\n\ncan efficiently support:\n\n\n\n    find({ user })\n\n    find({ user, date })\n\n    find({ user, date, type })\n\n\nBut not:\n\n\n\n    find({ date })\n\n    find({ type })\n\n    find({ date, type })\n\n\nbecause those queries do not start from the leftmost field.\n\n#  Back to My Original Doubt\n\nI originally saw:\n\n\n\n    transactionSchema.index({ user: 1, date: -1 });\n    transactionSchema.index({ user: 1, type: -1 });\n    transactionSchema.index({ user: 1, category: -1 });\n\n\nNow it makes sense.\n\n###  Recent Transactions\n\n\n    find({ user }).sort({ date: -1 });\n\n\nUses:\n\n\n\n    { user: 1, date: -1 }\n\n\n###  Filter By Transaction Type\n\n\n    find({\n      user,\n      type: \"expense\"\n    });\n\n\nUses:\n\n\n\n    { user: 1, type: -1 }\n\n\n###  Filter By Category\n\n\n    find({\n      user,\n      category: \"food\"\n    });\n\n\nUses:\n\n\n\n    { user: 1, category: -1 }\n\n\nEach index is optimized for a different query pattern.\n\n#  Another Question I Had: Where Are Indexes Stored?\n\nInitially, I thought indexes somehow reorganized the actual documents.\n\nBut that's not what happens.\n\nMongoDB stores documents and indexes separately.\n\nConceptually:\n\n\n\n    Collection\n    -----------\n    Doc1\n    Doc2\n    Doc3\n    Doc4\n\n\nAnd separately:\n\n\n\n    Index(user,date)\n    ----------------\n    Aarthi -> Doc5\n    Aarthi -> Doc2\n    Aarthi -> Doc1\n\n    Rosy -> Doc8\n\n\n\n    Index(user,type)\n    ----------------\n    Aarthi -> expense -> Doc1\n    Aarthi -> income  -> Doc2\n\n    Rosy -> expense -> Doc8\n\n\n\n    Index(user,category)\n    --------------------\n    Aarthi -> food -> Doc1\n    Aarthi -> travel -> Doc2\n\n    Rosy -> food -> Doc8\n\n\nThe actual documents remain unchanged.\n\nIndexes are separate data structures that contain references to documents.\n\n#  Then Why Do We Need `.sort()` If the Index Is Already Sorted?\n\nThis confused me too.\n\nSuppose we have:\n\n\n\n    {\n      user: 1,\n      date: -1\n    }\n\n\nThe index itself is sorted.\n\nHowever, MongoDB does not guarantee that results should be returned in date order unless we explicitly request it.\n\nFor example:\n\n\n\n    db.transactions.find({\n      user: \"Aarthi\"\n    });\n\n\nThis may use the index to locate records quickly.\n\nBut:\n\n\n\n    db.transactions.find({\n      user: \"Aarthi\"\n    }).sort({\n      date: -1\n    });\n\n\ntells MongoDB:\n\n> \"Return these records in descending date order.\"\n\nSince the index is already sorted that way, MongoDB can use the index directly and avoid an expensive in-memory sort.\n\nThat's one of the biggest performance benefits of compound indexes.\n\n#  How Does MongoDB Handle Multiple Indexes?\n\nThis was another question I had.\n\nSuppose we have:\n\n\n\n    { user: 1, date: -1 }\n\n    { user: 1, type: 1 }\n\n    { user: 1, category: 1 }\n\n\nMongoDB creates three completely separate index structures.\n\nThink of them as three separate books:\n\n###  Index 1\n\n\n    Aarthi\n      2025-08-10\n      2025-08-09\n\n    Rosy\n      2025-08-10\n\n\n###  Index 2\n\n\n    Aarthi\n      expense\n      income\n\n    Rosy\n      expense\n\n\n###  Index 3\n\n\n    Aarthi\n      food\n      travel\n\n    Rosy\n      shopping\n\n\nWhen a query arrives, MongoDB's query planner decides which index can answer the query most efficiently.\n\nExample:\n\n\n\n    find({\n      user: \"Aarthi\",\n      type: \"expense\"\n    });\n\n\nMongoDB sees:\n\n\n\n    { user: 1, type: 1 }\n\n\nand chooses that index.\n\nFor:\n\n\n\n    find({\n      user: \"Aarthi\"\n    }).sort({\n      date: -1\n    });\n\n\nMongoDB chooses:\n\n\n\n    { user: 1, date: -1 }\n\n\nbecause it perfectly matches the query.\n\n#  Why Not Create One Huge Index?\n\nI also wondered:\n\n\n\n    {\n      user: 1,\n      date: -1,\n      type: 1,\n      category: 1\n    }\n\n\nWouldn't this solve everything?\n\nNot really.\n\nBecause of the Prefix Rule.\n\nThis index efficiently supports:\n\n\n\n    find({ user })\n\n    find({ user, date })\n\n    find({ user, date, type })\n\n\nBut:\n\n\n\n    find({\n      user,\n      category\n    });\n\n\nis not optimal because `date` and `type` appear before `category` in the index definition.\n\nMongoDB cannot efficiently skip the middle fields.\n\nThat's why index design should follow actual query patterns rather than simply including every field.\n\n#  The Trade-Off Most Beginners Miss\n\nIndexes speed up reads.\n\nBut they are not free.\n\nEvery insert, update, or delete must also update all related indexes.\n\nFor example, when inserting:\n\n\n\n    {\n      user: \"Aarthi\",\n      date: \"2025-08-10\",\n      type: \"expense\",\n      category: \"food\"\n    }\n\n\nMongoDB must update:\n\n\n\n    Index(user,date)\n\n    Index(user,type)\n\n    Index(user,category)\n\n\nevery single time.\n\nSo indexes improve read performance at the cost of:\n\n  * Additional storage\n  * Slower writes\n  * Extra maintenance\n\n\n\nThis is the classic database trade-off.\n\n#  My Biggest Takeaway\n\nBefore this, I thought:\n\n> \"Indexes make queries faster.\"\n\nNow I think:\n\n> \"Indexes make specific query patterns faster.\"\n\nUnderstanding compound indexes, how MongoDB stores them, and the Prefix Rule completely changed the way I think about database design.\n\nThe best index is not the one with the most fields.\n\nThe best index is the one that matches the queries your application runs most often.\n\nSometimes a simple question like:\n\n> \"Why do we have 3 indexes for the same schema?\"\n\ncan lead to understanding an entire database concept.\n\nIf you've had a similar \"aha!\" moment while learning databases, I'd love to hear it in the comments.",
  "title": "MongoDB Indexes Finally Clicked for Me: Understanding Indexes, Compound Indexes & the Prefix Rule 🚀"
}