SELECT 
  p.promotion_id, 
  p.from_date, 
  p.to_date, 
  active.from_date AS active_from_date, 
  active.to_date AS active_to_date, 
  expired.from_date AS expired_from_date, 
  expired.to_date AS expired_to_date 
FROM 
  cscart_promotions AS p 
  INNER JOIN cscart_ab__dotd AS ab__dotd ON p.promotion_id = ab__dotd.promotion_id 
  AND ab__dotd.use_schedule = 'Y' 
  LEFT JOIN (
    SELECT 
      promotion_id, 
      MIN(from_date) AS from_date, 
      MIN(to_date) AS to_date 
    FROM 
      cscart_ab__dotd_periods 
    WHERE 
      to_date >= 1726730239 
    GROUP BY 
      promotion_id
  ) AS active ON active.promotion_id = p.promotion_id 
  LEFT JOIN (
    SELECT 
      promotion_id, 
      MAX(from_date) AS from_date, 
      MAX(to_date) AS to_date 
    FROM 
      cscart_ab__dotd_periods 
    WHERE 
      to_date < 1726730239 
    GROUP BY 
      promotion_id
  ) AS expired ON expired.promotion_id = p.promotion_id 
WHERE 
  p.status IN ('A', 'H')

Query time 0.00054

JSON explain

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "p",
      "access_type": "ALL",
      "possible_keys": ["PRIMARY"],
      "rows": 9,
      "filtered": 100,
      "attached_condition": "p.`status` in ('A','H')"
    },
    "table": {
      "table_name": "ab__dotd",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY"],
      "key": "PRIMARY",
      "key_length": "3",
      "used_key_parts": ["promotion_id"],
      "ref": ["admin_test.p.promotion_id"],
      "rows": 1,
      "filtered": 100,
      "attached_condition": "ab__dotd.use_schedule = 'Y'"
    },
    "table": {
      "table_name": "<derived2>",
      "access_type": "ref",
      "possible_keys": ["key0"],
      "key": "key0",
      "key_length": "4",
      "used_key_parts": ["promotion_id"],
      "ref": ["admin_test.p.promotion_id"],
      "rows": 2,
      "filtered": 100,
      "materialized": {
        "query_block": {
          "select_id": 2,
          "filesort": {
            "sort_key": "cscart_ab__dotd_periods.promotion_id",
            "temporary_table": {
              "table": {
                "table_name": "cscart_ab__dotd_periods",
                "access_type": "ALL",
                "possible_keys": ["PRIMARY"],
                "rows": 1,
                "filtered": 100,
                "attached_condition": "cscart_ab__dotd_periods.to_date >= 1726730239"
              }
            }
          }
        }
      }
    },
    "table": {
      "table_name": "<derived3>",
      "access_type": "ref",
      "possible_keys": ["key0"],
      "key": "key0",
      "key_length": "4",
      "used_key_parts": ["promotion_id"],
      "ref": ["admin_test.p.promotion_id"],
      "rows": 2,
      "filtered": 100,
      "materialized": {
        "query_block": {
          "select_id": 3,
          "filesort": {
            "sort_key": "cscart_ab__dotd_periods.promotion_id",
            "temporary_table": {
              "table": {
                "table_name": "cscart_ab__dotd_periods",
                "access_type": "ALL",
                "possible_keys": ["PRIMARY"],
                "rows": 1,
                "filtered": 100,
                "attached_condition": "cscart_ab__dotd_periods.to_date < 1726730239"
              }
            }
          }
        }
      }
    }
  }
}