MongoDB Indexes | B-Tree, Compound, and Query Optimization

# CHAPTER 12

MongoDB Indexing and Performance #

1. Introduction #

2. Learning Objectives #

• Understand the catastrophic performance of a "Collection Scan".

• Create Single Field Indexes using createIndex().

• Create Compound Indexes for multi-field queries.

• Utilize Text Indexes for search engines.

• Diagnose slow queries using the explain() method.

3. The Problem: Collection Scans (COLLSCAN) #

Because the database doesn't know where John's document is physically located on the hard drive, it must start at Document #1 and read every single document until it finds him. This is called a Collection Scan. It is incredibly slow, burns massive CPU power, and will cause your application to crash under heavy traffic.

4. Creating a Single Field Index #

// Build a hidden, alphabetically sorted B-Tree structure in the background!
// 1 = Ascending order, -1 = Descending order
db.users.createIndex({ email: 1 })

How it works: MongoDB creates a highly optimized data structure called a B-Tree (Balanced Tree). Now, when you search for "john", MongoDB jumps straight to the "J" section of the tree, instantly grabs the exact hard-drive location of the document, and fetches it in 0.001 seconds.

5. Unique Indexes #

// Force the database to instantly reject any duplicate emails!
db.users.createIndex({ email: 1 }, { unique: true })

6. Compound Indexes #

// Query: Find users in "CA" who are 25 years old.
// Solution: Create an index combining both fields!
db.users.createIndex({ state: 1, age: -1 })

7. Text Indexes (Building a Search Engine) #

// 1. Create the Text Index on the 'content' field
db.posts.createIndex({ content: "text" })

// 2. Perform a Google-style linguistic search!
db.posts.find({ $text: { $search: "MongoDB" } })

8. Diagnosing Queries: explain() #

db.users.find({ email: "john@example.com" }).explain("executionStats")

Look at the output:

• If winningPlan.stage says COLLSCAN, your query is dangerously slow.

• If it says IXSCAN (Index Scan), congratulations! Your index is working perfectly.

9. Common Mistakes #

• Indexing Every Single Field: Beginners think "If indexes make things fast, I should index every field!" This is a fatal mistake. Indexes consume massive amounts of RAM and hard drive space. Furthermore, every time you insertOne() document, MongoDB has to pause and update all the B-Trees. Too many indexes will make your Insert operations agonizingly slow.

• Compound Index Order matters: An index on { state: 1, age: 1 } is like a phone book sorted by State, then Age. If you try to query find({ age: 25 }), the index is useless because the book is sorted by State first! (This is called the ESR Rule: Equality, Sort, Range).

10. Best Practices #

• Drop Unused Indexes: Periodically review your indexes. If an index is no longer used by the application, drop it using db.collection.dropIndex("index_name"). It is consuming RAM and slowing down your writes.

11. Exercises #

1. 1. Write the command to create an Ascending index on the username field in the accounts collection.

1. 2. What diagnostic method do you append to a find() query to see if it is performing a COLLSCAN or an IXSCAN?

12. MongoDB Challenges #

db.products.createIndex({ category: 1, price: -1 })

13. MCQ Quiz with Answers #

14. Interview Questions #

• Q: Explain the mechanical difference between a COLLSCAN (Collection Scan) and an IXSCAN (Index Scan) in MongoDB.

• Q: If you have a Compound Index defined as { lastname: 1, firstname: 1 }, will MongoDB be able to utilize this index efficiently if a query only searches for { firstname: "John" }? Why or why not?

16. Summary #

17. Next Chapter Recommendation #