0
const d = new Date().getTime()+(1200*1000);
    const query = {
      userId: ObjectId(userId),
      timestamp: {
        $lte: d
      },
      $or: [
        // {
        //   status: "Active"
        // },
        // {
        //   status: "Removed"
        // }
      ]
    }
    Db.orders.findOne(query, () => {
      console.log(`[1]:`, 1)
      return resolve();
    })

If I uncomment the statuses in the $or it will burst the cpu from 0.3% to 300%.

This is the index that was set on this collection: userId: 1, timestamp: 1, status: 1

If I try to find without the $or - like that: {status: "Active"}

It won't burst and won't cause any issue.

Why does it happening?

Raz Buchnik
  • 7,753
  • 14
  • 53
  • 96

1 Answers1

1

Refer

When using $or with that are equality checks for the value of the same field, use the $in operator instead of the $or operator.

Refer this comment which I got an answer few hours back

explain on both of your queries and on this new approach using $in will also help further.

Gibbs
  • 21,904
  • 13
  • 74
  • 138
  • When I have deleted the index the problem was solved. Its like that the index made collision or something – Raz Buchnik Jul 18 '20 at 18:30
  • You need index to make your queries faster. Did you recreate them? – Gibbs Jul 18 '20 at 18:35
  • There was 68 queries running at once using Promise.all and since I have deleted them there was no cpu burst anymore - so what I have said in the comment is wrong. I have brought back those entities that were deleted and put this index back. Why does it bursts the cpu? Can you tell me if totalKeysExamined are documents scanned using index? – Raz Buchnik Jul 18 '20 at 18:44
  • I have put the backup which stores those 68 entities and ran this command on a single shot - not in promise all as a burst, and it took 1115ms to execute only one query - that's huge time. What am I missing? and got: totalKeysExamined: 95904 and totalDocksExamined: 5 – Raz Buchnik Jul 18 '20 at 18:47
  • Yes, that makes sense. If each query has to do that, it has to do a lot of page swaps which could cause that. – Gibbs Jul 18 '20 at 18:51
  • I have just removed all of the indexes from the collection and from 1s the same query took only 68ms.. how come? maybe the compound indexes were not built properly? – Raz Buchnik Jul 18 '20 at 18:56
  • 68 queries took only 68ms? – Gibbs Jul 18 '20 at 19:03
  • when index was: `{userId: 1, timestamp: 1, status:1}` one query took 1300ms which is very very much time of course. When removed this index (and actually removed any index just for this test) the query took around 150ms. When created specific indexes (not compound) - separated, like this: `{userId:1}` and `{timestamp:1}` and `{status: 1}` the query took 1ms. Same query on all tests. Mongo probably takes each property (key) from the query and first checks the count of the results, then he iterates where there the minimum of results, so if there only 5 results, the query will be n(5) – Raz Buchnik Jul 18 '20 at 19:11
  • Ok, Problem with compound index which means query plan didn't user the compound index which you created. It used COLLSCAN without index. If you change the order in your query i.e status as first instead userId, it won't use the compound index. When you create individual index, it chooses one index and does COLLSCAN on that. – Gibbs Jul 18 '20 at 19:27