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

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

    XMLWordPrintableJSON

Details

    • Icon: Question Question
    • Resolution: Done
    • Icon: Critical - P2 Critical - P2
    • None
    • 2.0.5
    • Querying
    • linux

    Description

      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?)

      Attachments

        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

        Activity

          People

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

            Dates

              Created:
              Updated:
              Resolved: