Slow execution of a query

Working with Data
1

Meteor version 2.0

MongoDB version 4.2

MongoDB query in MeteorJS takes 20-40 seconds to execute. I have a MongoDB collection of 10million+ documents of OHLCV price. Find query takes 0.2 seconds, but when I use fetch() (similar to toArray) in MeteorJS it increases to 20-40 seconds. The fields in the query are indexed. I can’t figure out what is causing the fetch() function to be so slow.

Document Example:

{
    "_id" : ObjectId("614d9a25416f6bffac1717bc"),
    "exchange" : "Tesla Crypto",
    "tickerFormatted" : "SUSHI/USCR",
    "ticker" : "SUSHIUSCR",
    "symbol0" : "SushiSwap",
    "symbol1" : "Crypto Dollar",
    "timeframe" : "1m",
    "start" : 1632475620000.0,
    "end" : 1632475679999.0,
    "open" : 9.5487,
    "high" : 9.5694,
    "low" : 9.42,
    "close" : 9.4409,
    "volume" : 0
}

Indexes:

  1. _id: 1
  2. tickerFormatted: 1
  3. timeframe: 1
  4. start: -1

MongoDB query (returns around 1000 documents out of 10+ million):

OHLCV_historical.find({
    tickerFormatted: ticker,
    timeframe,
    start: { $gte: from, $lte: to }
  }, { 
    fields: { 
      start: 1,
      low: 1,
      high: 1,
      open: 1,
      close: 1,
      volume: 1
    }
  }).fetch();

Tried removing/changing certain indexes, removing start field from the query, it didn’t work. Would really like to know what’s causing the issue.

2

Hello @dev7_gvisp, welcome to the MongoDB Community forum!

Your query filter has three fields, and this can be supported by a Compound Index on three fields (rather than the existing three single field indexes), and this can possibly aid to get a better performance.

You can measure query performance by applying the explain method on the query. This generates a Query Plan, and tells about how the index is used. The explain method has the executionStats verbosity which generates a plan with details like execution time.

1 Like