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 >= 1743802291 
    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 < 1743802291 
    GROUP BY 
      promotion_id
  ) AS expired ON expired.promotion_id = p.promotion_id 
WHERE 
  p.status IN ('A', 'H')

Query time 0.00031

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 0.04678372,
    "nested_loop": [
      {
        "table": {
          "table_name": "p",
          "access_type": "ALL",
          "possible_keys": ["PRIMARY"],
          "loops": 1,
          "rows": 7,
          "cost": 0.011130435,
          "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": ["cscart.p.promotion_id"],
          "loops": 7,
          "rows": 1,
          "cost": 0.00708028,
          "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": ["cscart.p.promotion_id"],
          "loops": 7,
          "rows": 1,
          "cost": 0.014286502,
          "filtered": 100,
          "materialized": {
            "query_block": {
              "select_id": 2,
              "cost": 0.004008444,
              "filesort": {
                "sort_key": "cscart_ab__dotd_periods.promotion_id",
                "temporary_table": {
                  "nested_loop": [
                    {
                      "table": {
                        "table_name": "cscart_ab__dotd_periods",
                        "access_type": "ALL",
                        "possible_keys": ["PRIMARY"],
                        "loops": 1,
                        "rows": 3,
                        "cost": 0.0113438,
                        "filtered": 100,
                        "attached_condition": "cscart_ab__dotd_periods.to_date >= 1743802291"
                      }
                    }
                  ]
                }
              }
            }
          }
        }
      },
      {
        "table": {
          "table_name": "<derived3>",
          "access_type": "ref",
          "possible_keys": ["key0"],
          "key": "key0",
          "key_length": "4",
          "used_key_parts": ["promotion_id"],
          "ref": ["cscart.p.promotion_id"],
          "loops": 7,
          "rows": 1,
          "cost": 0.014286502,
          "filtered": 100,
          "materialized": {
            "query_block": {
              "select_id": 3,
              "cost": 0.004008444,
              "filesort": {
                "sort_key": "cscart_ab__dotd_periods.promotion_id",
                "temporary_table": {
                  "nested_loop": [
                    {
                      "table": {
                        "table_name": "cscart_ab__dotd_periods",
                        "access_type": "ALL",
                        "possible_keys": ["PRIMARY"],
                        "loops": 1,
                        "rows": 3,
                        "cost": 0.0113438,
                        "filtered": 100,
                        "attached_condition": "cscart_ab__dotd_periods.to_date < 1743802291"
                      }
                    }
                  ]
                }
              }
            }
          }
        }
      }
    ]
  }
}