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 
  vi_promotions AS p 
  INNER JOIN vi_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 
      vi_ab__dotd_periods 
    WHERE 
      to_date >= 1776437359 
    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 
      vi_ab__dotd_periods 
    WHERE 
      to_date < 1776437359 
    GROUP BY 
      promotion_id
  ) AS expired ON expired.promotion_id = p.promotion_id 
WHERE 
  p.status IN ('A', 'H')

Query time 0.00017

JSON explain

{
  "query_block": {
    "select_id": 1,
    "nested_loop": [
      {
        "table": {
          "table_name": "<derived2>",
          "access_type": "system",
          "rows": 0,
          "filtered": 0,
          "const_row_not_found": true,
          "materialized": {
            "query_block": {
              "select_id": 2,
              "table": {
                "message": "no matching row in const table"
              }
            }
          }
        }
      },
      {
        "table": {
          "table_name": "<derived3>",
          "access_type": "system",
          "rows": 0,
          "filtered": 0,
          "const_row_not_found": true,
          "materialized": {
            "query_block": {
              "select_id": 3,
              "table": {
                "message": "no matching row in const table"
              }
            }
          }
        }
      },
      {
        "table": {
          "table_name": "ab__dotd",
          "access_type": "ALL",
          "possible_keys": ["PRIMARY"],
          "rows": 5,
          "filtered": 100,
          "attached_condition": "ab__dotd.use_schedule = 'Y'"
        }
      },
      {
        "table": {
          "table_name": "p",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "3",
          "used_key_parts": ["promotion_id"],
          "ref": ["devvipiriscom.ab__dotd.promotion_id"],
          "rows": 1,
          "filtered": 100,
          "attached_condition": "p.`status` in ('A','H')"
        }
      }
    ]
  }
}