I was building a joining utility, in order to retrieve subdocuments, I built a query of or clauses matching each sub document to retrieve. The results were not terribly surprising at first, the query was very slow to respond.

What was surprising was when I began to page my actual queries, that is to say divide the entries in the $or clause into many separate db queries matching 50 of the joining documents at a time, the database began responding much much faster.

A join query for 1000 document took a minute without paging. With a small page size of 5 documents per join query, that time went down to 177ms.

Querying for one document at a time was still at 214ms, way faster than where I started.

So what I mean to say is that I think there is something wrong with the $or clause if it basically takes longer to execute a single query rather that 1000 small queries.

Perhaps there is a limitation I'm not aware and that's why I marked this as an improvement and not a bug

Here are the results of my testing
Documents Page Size Total Time (ms)
1000 200 1236
1000 100 733
1000 50 407
1000 25 271
1000 10 192
1000 5 177
1000 1 214

10000 1000 59929
10000 500 30566
10000 200 12918
10000 100 6708
10000 50 4082
10000 25 2804
10000 10 1892
10000 5 1696
10000 1 2550