How do I index an $or predicate?

on
As the MongoDB Manual explains, it is possible to index a query with an $or condition, but only if all branches of the $or are supported by indexes.
For example, MongoDB can use indexes for the following query, but only if there are two separate indexes supporting both the predicates on {is:blocked: true} and {is_quarantined: true}:
{ $query:
    { $or: [ { is_blocked: true },
             { is_quarantined: true }
           ] },
    $orderby: { last_name: 1, first_name: 1, email: 1 }
}
You can also use indexes to avoid an in-memory or on-disk sort. If you provide an index that supports the predicates and the sort clause for all branches of the $or, MongoDB can replace a SORT operation with a much more efficient SORT_MERGE.
To verify this, execute the following query to view the execution plan without supporting indexes.:
db.tenant_users.find({ $or: [ { is_blocked: true }, { is_quarantined: true } ] })
               .sort({ last_name: 1, first_name: 1, email: 1 })
               .explain()
Compare to the execution plan of the same query after creating supporting indexes:
db.tenant_users.createIndex({ is_blocked: 1, last_name: 1, first_name: 1, email: 1 });
db.tenant_users.createIndex({ is_quarantined: 1, last_name: 1, first_name: 1, email: 1 });
db.tenant_users.find({ $or: [ { is_blocked: true }, { is_quarantined: true } ] })
               .sort({ last_name: 1, first_name: 1, email: 1 })
               .explain()

Comments

Post a Comment