Index direction (ascending vs descending) in MongoDB - Visual Side-by-Side Comparison

Concept Flow - Index direction (ascending vs descending)

Create Index

↓

Specify Field and Direction

↓

Ascending (1) or Descending (-1)?

↓

Ascending: Sorts from smallest to largest

↓

Descending: Sorts from largest to smallest

↓

Index Built with Direction

↓

Query Uses Index for Sorting

When creating an index in MongoDB, you specify the field and direction (1 for ascending, -1 for descending). This direction controls how the index sorts the data, which affects query performance when sorting.

Execution Sample

MongoDB

db.collection.createIndex({ age: 1 })
db.collection.createIndex({ age: -1 })

Creates two indexes on the 'age' field: one ascending and one descending.

Execution Table

Step	Action	Field	Direction	Index State	Effect on Query
1	Create index	age	1 (ascending)	Index on age ascending created	Queries sorting age ascending use this index efficiently
2	Create index	age	-1 (descending)	Index on age descending created	Queries sorting age descending use this index efficiently
3	Query with sort age ascending	age	1	Uses ascending index	Fast sort from smallest to largest age
4	Query with sort age descending	age	-1	Uses descending index	Fast sort from largest to smallest age
5	Query with sort age ascending but only descending index exists	age	1	No matching ascending index	Query slower, may need in-memory sort

💡 Index direction determines which queries can use the index for sorting; if direction mismatches, index may not be used.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
Index on age	None	Ascending (1)	Ascending (1) + Descending (-1)	Ascending (1) + Descending (-1)	Ascending (1) + Descending (-1)	Ascending (1) + Descending (-1)

Key Moments - 2 Insights

Why do we specify 1 or -1 when creating an index?

1 means ascending order (smallest to largest), -1 means descending order (largest to smallest). This affects how MongoDB sorts data using the index, as shown in execution_table rows 1 and 2.

Can a descending index be used for ascending sort queries?

No, MongoDB uses the index direction to optimize sorting. If the query sort direction doesn't match the index direction, the index won't be used for sorting efficiently (see execution_table row 5).

Visual Quiz - 3 Questions

Look at the execution table, which step shows the creation of a descending index?

A Step 2

B Step 3

C Step 1

D Step 4

💡 Hint

Check the 'Direction' column for '-1 (descending)' in the execution_table.

At which step does a query use the ascending index for sorting?

A Step 4

B Step 3

C Step 5

D Step 2

💡 Hint

Look for 'Uses ascending index' in the 'Index State' column in the execution_table.

If only an ascending index exists, what happens when a query sorts descending?

A Uses ascending index efficiently

B Uses descending index efficiently

C No matching index, slower query

D Query fails

💡 Hint

See execution_table row 5 about query with sort direction mismatch.

Concept Snapshot

MongoDB indexes can be ascending (1) or descending (-1).
Direction controls how data is sorted in the index.
Queries sorting in the same direction use the index efficiently.
If directions mismatch, index may not be used for sorting.
Specify direction when creating index: { field: 1 } or { field: -1 }.

Full Transcript

In MongoDB, when you create an index, you specify the field and the direction: 1 for ascending or -1 for descending. This direction tells MongoDB how to sort the data in the index. For example, an ascending index sorts from smallest to largest values, while a descending index sorts from largest to smallest. When you run queries that sort data, MongoDB uses the index that matches the sort direction to speed up the query. If the query's sort direction does not match any index direction, MongoDB cannot use the index for sorting efficiently, which may slow down the query. This visual trace showed creating ascending and descending indexes on the 'age' field, and how queries use these indexes depending on their sort direction.

Practice

1. What does the index direction 1 mean in MongoDB indexes?

easy

A. Descending order

B. No order

C. Ascending order

D. Random order

Solution

1. Step 1: Understand index direction values

   In MongoDB, index direction 1 means ascending order, and -1 means descending order.

2. Step 2: Match the value to the meaning

   Since the question asks about 1, it corresponds to ascending order.

3. Final Answer:

   Ascending order -> Option C

4. Quick Check:

   Index direction 1 = Ascending order [OK]

Hint: 1 means ascending, -1 means descending [OK]

Common Mistakes:

• Confusing 1 with descending order
• Thinking 1 means no order
• Mixing up ascending and descending

2. Which of the following is the correct syntax to create a descending index on the field age in MongoDB?

easy

A. db.collection.createIndex({ age: 1 })

B. db.collection.createIndex({ age: -1 })

C. db.collection.createIndex({ age: 'desc' })

D. db.collection.createIndex({ age: 'descending' })

Solution

1. Step 1: Recall MongoDB index syntax for direction

   MongoDB uses 1 for ascending and -1 for descending in index creation.

2. Step 2: Identify the correct descending syntax

   The correct way to create a descending index on age is { age: -1 }.

3. Final Answer:

   db.collection.createIndex({ age: -1 }) -> Option B

4. Quick Check:

   Descending index uses -1 [OK]

Hint: Use -1 for descending index direction [OK]

Common Mistakes:

• Using string values like 'desc' instead of -1
• Using 1 which means ascending
• Incorrect object syntax

3. Given the index { name: 1, score: -1 }, which query will use this index efficiently?

medium

A. db.collection.find().sort({ name: 1, score: -1 })

B. db.collection.find().sort({ name: -1, score: 1 })

C. db.collection.find().sort({ score: -1, name: 1 })

D. db.collection.find().sort({ name: 1 })

Solution

1. Step 1: Understand compound index direction

   The index sorts name ascending and score descending.

2. Step 2: Match query sort order with index

   Only the query sorting { name: 1, score: -1 } matches the index directions exactly, so it uses the index efficiently.

3. Final Answer:

   db.collection.find().sort({ name: 1, score: -1 }) -> Option A

4. Quick Check:

   Sort matches index directions exactly [OK]

Hint: Sort order must match index directions exactly [OK]

Common Mistakes:

• Reversing sort directions
• Changing field order in sort
• Using partial sort that doesn't match index

4. You created an index with { date: 1, price: -1 } but your query sorts by { price: -1, date: 1 }. What is the problem?

medium

A. The index fields order does not match the sort order

B. The index direction values are invalid

C. MongoDB does not support mixed ascending and descending indexes

D. The query syntax is incorrect

Solution

1. Step 1: Check index field order

   The index is created with fields date then price.

2. Step 2: Compare query sort order

   The query sorts by price first, then date, which does not match the index field order.

3. Final Answer:

   The index fields order does not match the sort order -> Option A

4. Quick Check:

   Field order in sort must match index order [OK]

Hint: Index and sort field order must match exactly [OK]

Common Mistakes:

• Assuming direction values are invalid
• Thinking MongoDB disallows mixed directions
• Blaming query syntax instead of order

5. You want to optimize queries that sort by category ascending and then by rating descending. Which index should you create?

hard

A. db.collection.createIndex({ rating: -1, category: 1 })

B. db.collection.createIndex({ category: -1, rating: 1 })

C. db.collection.createIndex({ rating: 1, category: -1 })

D. db.collection.createIndex({ category: 1, rating: -1 })

Solution

1. Step 1: Identify query sort order

   The queries sort by category ascending and rating descending.

2. Step 2: Create matching compound index

   The index must have category with direction 1 and rating with direction -1 in that order.

3. Final Answer:

   db.collection.createIndex({ category: 1, rating: -1 }) -> Option D

4. Quick Check:

   Index matches query sort directions and order [OK]

Hint: Match index fields and directions to query sort exactly [OK]

Common Mistakes:

• Reversing field order in index
• Swapping ascending and descending directions
• Creating index on wrong fields