Composite index requirements in Firebase - Step-by-Step Execution

Process Flow - Composite index requirements

Start Query

↓

Check Query Filters

↓

Are multiple fields filtered or sorted?

No→Use Single-field Index

Yes↓

Check Composite Index Exists

↓

If Exists

→Query Uses Composite Index

↓

If Not Exists

→Error: Create Composite Index

↓

Create Composite Index in Firebase Console

↓

Retry Query

↓

Query Succeeds Using Composite Index

When a query filters or sorts on multiple fields, Firebase requires a composite index. If missing, it prompts to create one before the query can succeed.

Execution Sample

Firebase

db.collection('books')
  .where('author', '==', 'Alice')
  .orderBy('year')
  .get()

This query filters books by author and sorts by year, requiring a composite index on 'author' and 'year'.

Process Table

Step	Query Part	Index Check	Action	Result
1	Filter on 'author'	Single-field index exists	Use single-field index	Continue
2	Order by 'year'	Needs composite index with 'author' and 'year'	Check composite index	Not found
3	Composite index missing	Error thrown	Prompt to create composite index	Query fails
4	Create composite index	Index created in Firebase Console	Retry query	Query succeeds

💡 Query stops at step 3 because composite index is missing for multiple fields filter and sort.

Status Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4
Query Status	Not started	Filtering on author OK	Composite index missing error	Error shown to user	Query succeeds after index creation

Key Moments - 2 Insights

Why does the query fail even though single-field indexes exist?

Because the query filters and sorts on multiple fields, Firebase requires a composite index combining those fields (see execution_table step 3). Single-field indexes alone are not enough.

What should I do when Firebase says a composite index is missing?

You need to create the composite index in the Firebase Console as prompted (see execution_table step 4), then retry the query.

Visual Quiz - 3 Questions

Look at the execution table, at which step does the query fail due to missing composite index?

A Step 2

B Step 4

C Step 3

D Step 1

💡 Hint

Check the 'Result' column in execution_table row for step 3.

According to variable_tracker, what is the query status after step 4?

A Query fails with error

B Query succeeds after index creation

C Query not started

D Filtering on author OK

💡 Hint

Look at the last column 'After Step 4' in variable_tracker for 'Query Status'.

If the query only filtered on 'author' without ordering by 'year', what index would be needed?

A Single-field index on 'author'

B No index needed

C Composite index on 'author' and 'year'

D Composite index on 'year' only

💡 Hint

Refer to execution_table step 1 where filtering on 'author' uses single-field index.

Concept Snapshot

Composite index is needed when a query filters or sorts on multiple fields.
Firebase checks if such an index exists.
If missing, query fails with an error.
Create the composite index in Firebase Console.
Retry query to succeed.

Full Transcript

When you run a Firebase query that filters or sorts on more than one field, Firebase requires a composite index that covers those fields. The process starts by checking if single-field indexes suffice. If the query involves multiple fields, Firebase looks for a composite index. If it does not exist, the query fails and Firebase prompts you to create the composite index in the Firebase Console. After creating the index, retrying the query will succeed. This ensures queries are efficient and fast.

Practice

1. What is the main reason Firebase requires a composite index?

easy

A. To encrypt data for security

B. To store data in multiple locations

C. To backup data automatically

D. To efficiently query data using multiple fields together

Solution

1. Step 1: Understand composite index purpose

   Composite indexes allow Firebase to quickly search using more than one field at the same time.

2. Step 2: Compare options to purpose

   Only To efficiently query data using multiple fields together matches this purpose; others describe unrelated features.

3. Final Answer:

   To efficiently query data using multiple fields together -> Option D

4. Quick Check:

   Composite index = multi-field query speed [OK]

Hint: Composite indexes speed up queries with multiple fields [OK]

Common Mistakes:

• Confusing composite indexes with data backup
• Thinking composite indexes encrypt data
• Believing composite indexes store data in multiple places

2. Which of the following is the correct way to define a composite index in Firebase JSON?

easy

A. "indexes": [{"collectionGroup": "users", "queryScope": "COLLECTION", "fields": [{"fieldPath": "age", "order": "ASCENDING"}, {"fieldPath": "name", "order": "DESCENDING"}]}]

B. "indexes": [{"collection": "users", "fields": ["age", "name"]}]

C. "indexes": [{"collectionGroup": "users", "fields": [{"fieldPath": "age"}, {"fieldPath": "name"}]}]

D. "indexes": [{"collectionGroup": "users", "queryScope": "COLLECTION", "fields": ["age", "name"]}]

Solution

1. Step 1: Check JSON structure for composite index

   Correct composite index JSON includes collectionGroup, queryScope, and fields with fieldPath and order.

2. Step 2: Validate each option

   Only "indexes": [{"collectionGroup": "users", "queryScope": "COLLECTION", "fields": [{"fieldPath": "age", "order": "ASCENDING"}, {"fieldPath": "name", "order": "DESCENDING"}]}] has all required keys and correct field definitions with order specified.

3. Final Answer:

   "indexes": [{"collectionGroup": "users", "queryScope": "COLLECTION", "fields": [{"fieldPath": "age", "order": "ASCENDING"}, {"fieldPath": "name", "order": "DESCENDING"}]}] -> Option A

4. Quick Check:

   Composite index JSON needs fields with order [OK]

Hint: Composite index JSON needs fields with order and collectionGroup [OK]

Common Mistakes:

• Omitting order in fields
• Using 'collection' instead of 'collectionGroup'
• Listing fields as simple strings without objects

3. Given this query in Firebase: db.collection('products').where('category', '==', 'books').orderBy('price', 'asc').orderBy('rating', 'desc'), what composite index is required?

medium

A. Index on fields: category (DESC), price (ASC), rating (ASC)

B. Index on fields: price (ASC), rating (DESC)

C. Index on fields: category (ASC), price (ASC), rating (DESC)

D. No composite index needed

Solution

1. Step 1: Identify query filters and orderBy fields

   The query filters on 'category' and orders by 'price' ascending and 'rating' descending.

2. Step 2: Determine composite index fields and order

   Composite index must include 'category' (filter), then 'price' (asc), then 'rating' (desc) in that order.

3. Final Answer:

   Index on fields: category (ASC), price (ASC), rating (DESC) -> Option C

4. Quick Check:

   Filter + orderBy fields = composite index fields [OK]

Hint: Include all filter and orderBy fields with correct order [OK]

Common Mistakes:

• Ignoring filter field in index
• Wrong order or direction of fields
• Assuming no index needed for multiple orderBy

4. You get an error saying a composite index is missing for this query: db.collection('orders').where('status', '==', 'shipped').orderBy('date', 'desc'). Which fix is correct?

medium

A. Create a composite index on fields: status (ASC), date (DESC)

B. Create a composite index on fields: date (DESC), status (ASC)

C. Create a single-field index on date only

D. No index needed; fix query syntax

Solution

1. Step 1: Analyze query filter and orderBy

   The query filters on 'status' and orders by 'date' descending.

2. Step 2: Determine correct composite index order

   Composite index must start with filter field 'status' (ASC by default), then 'date' (DESC) as orderBy.

3. Final Answer:

   Create a composite index on fields: status (ASC), date (DESC) -> Option A

4. Quick Check:

   Filter field first, then orderBy fields in index [OK]

Hint: Filter field first in index, then orderBy fields [OK]

Common Mistakes:

• Reversing field order in index
• Creating single-field index only
• Ignoring error and changing query syntax

5. You want to query a collection 'events' filtering by 'location' and 'type', and sorting by 'date' ascending. Which composite index definition is correct?

hard

A. Index on fields: date (ASC), location (ASC), type (ASC)

B. Index on fields: location (ASC), type (ASC), date (ASC)

C. Index on fields: location (DESC), type (DESC), date (ASC)

D. No composite index needed if only filtering

Solution

1. Step 1: Identify query filter and sort fields

   The query filters on 'location' and 'type', and sorts by 'date' ascending.

2. Step 2: Determine composite index order

   Composite index must list filter fields first in any order, then the sort field last with ascending order.

3. Step 3: Validate options

   Index on fields: location (ASC), type (ASC), date (ASC) matches filter fields first (location, type) then date ascending. Index on fields: date (ASC), location (ASC), type (ASC) starts with date, which is incorrect. Index on fields: location (DESC), type (DESC), date (ASC) uses descending for filters, which is wrong. No composite index needed if only filtering is incorrect because sorting requires composite index.

4. Final Answer:

   Index on fields: location (ASC), type (ASC), date (ASC) -> Option B

5. Quick Check:

   Filters first, then sort field in index [OK]

Hint: Filter fields first, then sort field ascending in index [OK]

Common Mistakes:

• Placing sort field before filters in index
• Using descending order for filter fields
• Assuming no index needed when sorting