C100DBA practice question 25 of 25

If you have created a compound index on (A,B, C) which of the following access pattern will not be able to utilize the index?

Full set: 130 questions
Answer options
A: A, B, C
B: A
C: B, C Correct answer
D: A, B
Correct Answer:
C. B, C
Explanation

A compound index on (A, B, C) supports queries that filter on a prefix of the index keys, in order. This means it can efficiently serve queries on (A), (A, B), and (A, B, C). However, it cannot support a query that filters on (B, C) without also specifying a condition on A. The database cannot efficiently scan the index for B values without first traversing the index based on A. Therefore, the access pattern (B, C) will not be able to effectively utilize the index.

Why Incorrect

A. The query (A, B, C) matches the full index key, allowing for the most efficient use of the index.
B. The query (A) uses the first field of the index, which is a valid index prefix, so the index can be used.
D. The query (A, B) uses the first two fields of the index, which is a valid index prefix, so the index can be used.

References

1. MongoDB, Inc. (2023). Compound Indexes. MongoDB Manual. Retrieved from https://www.mongodb.com/docs/manual/core/index-compound/. See the section "Index Prefixes".

2. MongoDB, Inc. (2023). Create a Compound Index. MongoDB Manual. Retrieved from https://www.mongodb.com/docs/manual/tutorial/create-a-compound-index/. The documentation states, "The index can support queries that include the prefix fields... However, the index would not support a query that included only the stock and price fields." (using an example index on item, stock, price).

3. Chodorow, K. (2013). MongoDB: The Definitive Guide (2nd ed.). O'Reilly Media. Chapter 4, "Indexing", section on "Compound Indexes".

Scroll to Top