-
Type: Bug
-
Resolution: Duplicate
-
Priority: Major - P3
-
None
-
Affects Version/s: 2.0.6
-
Component/s: Querying
-
None
-
Environment:any
-
ALL
I have 2 sets of "ored" conditions because I need all rows that meet conditions A or B or C, and that also meet conditions D or E or F.
Since I can only have a single $or statement, I use this:
$or:[
,
{B},
{C}],$and[{$or:[
{D},
{E},
{F}]}]
This returns the correct results, and by the way, uses indexes on A, B and C and runs in parallel, which is fantastic (good job!!!).
However, if my indexes on D,E and F are even better choices than A,B and C, let's say they have better selectivity, the query optimizer completely ignores them as viable plans. Moreover, there is no way for me to give a hint to use them, because hint can only take a single index. Even if I had a combined index on D_E_F and passed it as a hint, it would not help because the query on E and on F would not be able to use it.
I think we need either a mechanism to specify multiple first level $ors (I know JS does not make it easy) or a smarter query optimizer that considers more alternatives, even if they are hidden inside an $and.
- duplicates
-
SERVER-3327 use indexes for nested $or clauses
- Closed