Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-11307

Large $or clauses are extremely slow when compared to other access methods

    • Type: Icon: Improvement Improvement
    • Resolution: Done
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 2.5.3
    • Component/s: Querying
    • Environment:
      nodejs driver
    • Fully Compatible

      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

            Assignee:
            ramon.fernandez@mongodb.com Ramon Fernandez Marina
            Reporter:
            khous Kyle Housley
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved: