Query patterns that cause collection scans in MongoDB - Mini Project: Build & Apply

Query Patterns That Cause Collection Scans in MongoDB

📖 Scenario: You are working with a MongoDB database for a small online bookstore. The database has a collection called books that stores information about each book, including its title, author, year of publication, and genre.Sometimes, queries run slowly because MongoDB has to scan the entire collection instead of using an index. This happens when queries use patterns that do not match any index.

🎯 Goal: Learn to write queries that cause collection scans in MongoDB by using patterns that do not use indexes. This will help you understand why some queries are slow and how to identify them.

📋 What You'll Learn

Create a books collection with specific documents.

Add an index on the author field.

Write a query that causes a collection scan by searching on a non-indexed field.

Write a query that causes a collection scan by using a regex pattern that cannot use the index.

💡 Why This Matters

🌍 Real World

Understanding which queries cause collection scans helps developers optimize database performance by creating appropriate indexes.

💼 Career

Database administrators and backend developers must know how to identify slow queries caused by collection scans to improve application responsiveness.

1

Create the books collection with sample documents

Create a variable called books and assign it an array of three documents with these exact fields and values: { title: "The Hobbit", author: "J.R.R. Tolkien", year: 1937, genre: "Fantasy" }, { title: "1984", author: "George Orwell", year: 1949, genre: "Dystopian" }, and { title: "To Kill a Mockingbird", author: "Harper Lee", year: 1960, genre: "Fiction" }. Then insert the documents into the books collection using db.books.insertMany(books).

MongoDB

const books = [
  // Your code here
];

Hint

Use an array with three objects exactly as shown, then db.books.insertMany(books);

2

Create an index on the author field

Write a line of code that creates an index on the author field of the books collection using db.books.createIndex({ author: 1 }).

MongoDB

const books = [
  { title: "The Hobbit", author: "J.R.R. Tolkien", year: 1937, genre: "Fantasy" },
  { title: "1984", author: "George Orwell", year: 1949, genre: "Dystopian" },
  { title: "To Kill a Mockingbird", author: "Harper Lee", year: 1960, genre: "Fiction" }
];

db.books.insertMany(books);

// Your code here

Hint

Use db.books.createIndex with { author: 1 } to create the index.

3

Write a query that causes a collection scan by searching on genre

Write a query using db.books.find() to find all books where genre is exactly "Fantasy". This query will cause a collection scan because genre is not indexed.

MongoDB

const books = [
  { title: "The Hobbit", author: "J.R.R. Tolkien", year: 1937, genre: "Fantasy" },
  { title: "1984", author: "George Orwell", year: 1949, genre: "Dystopian" },
  { title: "To Kill a Mockingbird", author: "Harper Lee", year: 1960, genre: "Fiction" }
];

db.books.insertMany(books);

db.books.createIndex({ author: 1 });

// Your code here

Hint

Use db.books.find with a filter on genre equal to "Fantasy".

4

Write a query that causes a collection scan using a regex on title

Write a query using db.books.find() to find all books where the title starts with the letter "T" using a regex: { title: /^T/ }. This query will cause a collection scan because title is not indexed and the regex cannot use an index.

MongoDB

const books = [
  { title: "The Hobbit", author: "J.R.R. Tolkien", year: 1937, genre: "Fantasy" },
  { title: "1984", author: "George Orwell", year: 1949, genre: "Dystopian" },
  { title: "To Kill a Mockingbird", author: "Harper Lee", year: 1960, genre: "Fiction" }
];

db.books.insertMany(books);

db.books.createIndex({ author: 1 });

db.books.find({ genre: "Fantasy" });

// Your code here

Hint

Use db.books.find with a regex filter on title starting with T.

Practice

1. Which of the following query patterns in MongoDB is most likely to cause a collection scan?

easy

A. Querying a field without an index

B. Querying a field with a single-field index

C. Using an indexed field with equality operator

D. Using a covered index query

Solution

1. Step 1: Understand collection scan causes

   A collection scan happens when MongoDB must check every document because no index supports the query.

2. Step 2: Analyze query patterns

   Querying a field without an index forces MongoDB to scan the entire collection, unlike queries using indexed fields.

3. Final Answer:

   Querying a field without an index -> Option A

4. Quick Check:

   No index = collection scan [OK]

Hint: No index on field? Expect collection scan [OK]

Common Mistakes:

• Assuming all queries use indexes automatically
• Confusing covered index with collection scan
• Thinking equality queries always cause scans

2. Which of the following MongoDB query syntaxes is correct and avoids collection scans by using an index on the field age?

easy

A. db.users.find({ age: { $gt: 25 } })

B. db.users.find({ $where: 'this.age > 25' })

C. db.users.find({ age: { $regex: '^2' } })

D. db.users.find({ $text: { $search: '25' } })

Solution

1. Step 1: Check query syntax correctness

   All queries are syntactically valid, but some use operators that prevent index use.

2. Step 2: Identify index-friendly query

   Query with { age: { $gt: 25 } } uses range operator on indexed field, avoiding collection scan. Queries with $where or $regex often cause scans.

3. Final Answer:

   db.users.find({ age: { $gt: 25 } }) -> Option A

4. Quick Check:

   Range query on indexed field = no scan [OK]

Hint: Use simple operators on indexed fields to avoid scans [OK]

Common Mistakes:

• Using $where causes collection scan
• Assuming $regex always uses index
• Confusing $text search with field index

3. Given a collection products with an index on category, what will be the result of this query?

db.products.find({ category: { $in: ['electronics', 'books'] } })

medium

A. Throws a syntax error due to $in usage

B. Performs a collection scan and returns all documents

C. Returns no documents because $in is unsupported

D. Uses index on category and returns matching documents

Solution

1. Step 1: Understand $in operator with indexes

   The $in operator can use indexes on the queried field to find matching values efficiently.

2. Step 2: Analyze query behavior

   Since category is indexed, MongoDB uses the index to find documents where category is 'electronics' or 'books'.

3. Final Answer:

   Uses index on category and returns matching documents -> Option D

4. Quick Check:

   $in + indexed field = index used [OK]

Hint: Indexed field + $in uses index, no scan [OK]

Common Mistakes:

• Thinking $in disables index use
• Assuming $in causes syntax error
• Believing collection scan always happens

4. You have a MongoDB collection orders with an index on status. Which query will cause a collection scan and how can you fix it?

db.orders.find({ $where: 'this.status === "shipped"' })

medium

A. Syntax error; fix by removing $where

B. Uses index; no fix needed

C. Causes collection scan; fix by using { status: 'shipped' }

D. Causes collection scan; fix by adding $regex

Solution

1. Step 1: Identify $where impact on query

   The $where operator runs JavaScript on each document, forcing a collection scan regardless of indexes.

2. Step 2: Fix query to use index

   Replacing $where with a direct field query { status: 'shipped' } uses the index on status, avoiding collection scan.

3. Final Answer:

   Causes collection scan; fix by using { status: 'shipped' } -> Option C

4. Quick Check:

   $where disables index; direct query fixes [OK]

Hint: Avoid $where; use direct field queries for indexes [OK]

Common Mistakes:

• Assuming $where uses indexes
• Trying to fix with $regex instead of direct query
• Confusing syntax error with collection scan

5. You want to optimize this MongoDB query to avoid collection scans on a large users collection:

db.users.find({ $or: [ { age: { $gt: 30 } }, { name: /John/ } ] })

Given indexes on age and name, which change will prevent collection scans?

hard

A. Remove indexes and rely on collection scan for speed

B. Rewrite query to separate $or into two queries using indexed fields

C. Use $where with JavaScript to filter documents

D. Change regex to case-insensitive without anchors

Solution

1. Step 1: Understand $or with mixed operators

   Regex queries often prevent index use, causing collection scans even if other fields are indexed.

2. Step 2: Optimize by splitting queries

   Running two separate queries--one on age and one on name--each using indexes, then combining results in application avoids scans.

3. Final Answer:

   Rewrite query to separate $or into two queries using indexed fields -> Option B

4. Quick Check:

   Split queries + indexes avoid scans [OK]

Hint: Split $or queries to use indexes separately [OK]

Common Mistakes:

• Believing regex always uses index
• Using $where increases scans
• Removing indexes reduces performance