$or query using more than 2 indexes takes forever

Hello,

I have a probleme with a mongo query that uses an $or like :

{
    a: 1,
    b: 2,
    $or: [
        { c: 3 },
        { d: 4 },
        { e: 5 },
        { f: 6 }
    ]
}

i have every a_b_c, a_b_d, a_b_e and a_b_f indexes but that query takes more than 150 seconds.

But, if i comment any 2 of the 4 items in my $or, the request takes 0.5 seconds

Does mongo applie a limit of index used in an $or query ? or something i didn’t get ?

Thanks for your answer.

Hi @Joris_SEBIRE

Welcome to MongoDB community.

The ways your indexes are built the engine tries to do an index intersection loading all indexes to memory and merging them.

I think that for this query its best to use either all fields in one index or just index a,b …

Thanks,
Pavel

Refer to this documentation that might be helpful for your use-case.

https://docs.mongodb.com/manual/core/index-compound/