Why is the index username_1 considered redundant if username_1_active_1 also exists?

The reason the index on username_1 is deemed redundant in the presence of the username_1_active_1 index is that it is a prefix of the latter. In MongoDB, when an index exists on a compound field like username_1_active_1, it inherently includes the values of username_1. Therefore, any queries that need to filter by username_1 can effectively utilize the username_1_active_1 index instead.

This means that the specific index on username_1 does not provide additional benefit because all lookups that would utilize username_1 can already be fulfilled by the more complex index that contains it as part of its structure. As a result, the index on username_1 is considered unnecessary and redundant, as it does not enhance query performance in any way that isn't already covered by the existing compound index. Other choices may not accurately capture this relationship or may suggest scenarios that do not affect the functional role of these indexes within MongoDB.