Extend db.collection.distinct() to work with multiple fields in a compound key
Currently the distinct() command finds the unique set of values for a SINGLE specified field across a collection or view. For example:
db.staff.distinct("last_name" )
If there is an index on the lastname field, the DISTINCTSCAN plan can use that index and the operation is very fast.
To find the unique values for a set of more than one fields, the $group aggregation stage has to be used like this:
db.staff.aggregate([
{$group: {id: {FName: "$firstname", LName: "$last_name"}} ]);
This operation does not really need the $group functionality, as it is not calculating a sum/min/max/average/etc value using the accumulator operators. It also runs much more slowly than a DISTICT_SCAN.
The ask here is to allow distinct() to be run over a set of fields, and use DISTINCT_SCAN if there is an index over the same fields.
William
shared this idea
-
When you're doing aggregation for this, it doesn't know to use an index - you would need to add `hint` or `$sort` to make it use an index. That's likely to make it faster (though it still wouldn't use the superior DISTINCT_SCAN plan, it will use a covered IXSCAN).
