Advanced Queries $gt and $lt take longer in 'indexed' collection

XMLWordPrintableJSON

    • Type: Question
    • Resolution: Done
    • Priority: Critical - P2
    • None
    • Affects Version/s: 2.0.5
    • Component/s: Querying
    • Environment:
      linux
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      My collection has 1 million data.

      I am performing this query to retrieve
      x > 70000 or x < 10000

      In case 1, the pid (player id) of the collection isn't indexed.
      a) if i query with {$or:[{pid:{$gt:700000}},{pid:{$lt:100000}}]},
      it took around 750ms
      b) if i query with {$or:[{pid:{$lt:100000}},{pid:{$gt:700000}}]}, (simply change the order of expression)
      it took around 800ms

      Everything is still ok.

      BUT in case 2, i indexed pid, which supposed to provide a faster query. However,
      a) if i query with {$or:[{pid:{$gt:700000}},{pid:{$lt:100000}}]},
      it took around 980ms
      b) WORSE, if i query with {$or:[{pid:{$lt:100000}},{pid:{$gt:700000}}]},
      it took around 1600ms!!

      I have 2 questions here:
      1. Why the indexed collection takes longer?
      2. Why different expressions could have such a big difference in the time of query? ( does it have something to do with $or?)

        1. indexOr.bmp
          indexOr.bmp
          1.09 MB
        2. indexOr2.bmp
          indexOr2.bmp
          1.10 MB
        3. no index.bmp
          no index.bmp
          274 kB

            Assignee:
            Kristina Chodorow (Inactive)
            Reporter:
            btd5nerds
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated:
              Resolved: