Query latest record based on createdDate group

[iRon]

So I am getting stock quote on a 1 min interval. However I am getting multiple record while the candle is updating so I need to query where the most recent createdDate is the closing candle for that second.

[
  {
    "SPFuture": {
      "close": 4154.75,
      "createdDate": "2023-05-15T01:28:40.7539802-07:00",
      "date": "2023-05-15T01:28:00",
      "high": 4154.75,
      "id": "6461ed384b35b7a4e5bdcf74",
      "low": 4154.25,
      "open": 4154.5,
      "volume": 106
    }
  },
  {
    "SPFuture": {
      "close": 4154.75,
      "createdDate": "2023-05-15T01:28:40.7518972-07:00",
      "date": "2023-05-15T01:28:00",
      "high": 4154.75,
      "id": "6461ed384b35b7a4e5bdcf6e",
      "low": 4154.25,
      "open": 4154.5,
      "volume": 108
    }
  },
  {
    "SPFuture": {
      "close": 4154.75,
      "createdDate": "2023-05-15T01:28:40.5053761-07:00",
      "date": "2023-05-15T01:28:00",
      "high": 4154.75,
      "id": "6461ed384b35b7a4e5bdcf68",
      "low": 4154.25,
      "open": 4154.5,
      "volume": 105
    }
  },

Getting timeout on this

SELECT spf.*
FROM SPFuture spf
    JOIN (
    SELECT createdDate,
           date
    FROM SPFuture ss
    WHERE ss.date>='2023-05-15T00:00:00'
        AND ss.date=spf.date
    ORDER BY createdDate DESC
    LIMIT 1) zz ON zz.createdDate=spf.createdDate
    AND zz.date=spf.date
WHERE date>='2023-05-15T00:00:00'
ORDER BY date DESC

[vsr1]

CREATE INDEX ix1 ON SPFuture( date DESC, createdDate DESC);

WITH docs AS (SELECT RAW MAX([sf.createdDate, META(sf).id])[1] 
                          FROM SPFuture AS sf
                          WHERE sf.date>='2023-05-15T00:00:00'
                          GROUP BY sf.date
                          ORDER BY sf.date DESC )
SELECT sf1.*
FROM SPFuture AS sf1 USE KEYS docs;

CTE, use covering index and (EE index aggregation) produce one document key per group and fetch only that document. You can also use just CTE and use SDK get actual document if those are many or high volume.

If you know exact min and want only the document in that range

SELECT sf.*
FROM SPFuture AS sf
WHERE sf.date = "2023-05-15T01:28:00"
ORDER BY sf.createdDate DESC
LIMIT 1;