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 
  tmall_promotions AS p 
  INNER JOIN tmall_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 
      tmall_ab__dotd_periods 
    WHERE 
      to_date >= 1765552468 
    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 
      tmall_ab__dotd_periods 
    WHERE 
      to_date < 1765552468 
    GROUP BY 
      promotion_id
  ) AS expired ON expired.promotion_id = p.promotion_id 
WHERE 
  p.status IN ('A', 'H')

Query time 0.00765

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost": 0.04435045,
    "nested_loop": [
      {
        "table": {
          "table_name": "ab__dotd",
          "access_type": "ALL",
          "possible_keys": ["PRIMARY"],
          "loops": 1,
          "rows": 5,
          "cost": 0.0116698,
          "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": ["tmall.ab__dotd.promotion_id"],
          "loops": 5,
          "rows": 1,
          "cost": 0.00884993,
          "filtered": 100,
          "attached_condition": "p.`status` in ('A','H')"
        }
      },
      {
        "table": {
          "table_name": "<derived2>",
          "access_type": "ref",
          "possible_keys": ["key0"],
          "key": "key0",
          "key_length": "4",
          "used_key_parts": ["promotion_id"],
          "ref": ["tmall.ab__dotd.promotion_id"],
          "loops": 5,
          "rows": 1,
          "cost": 0.01191536,
          "filtered": 100,
          "materialized": {
            "lateral": 1,
            "query_block": {
              "select_id": 2,
              "cost": 0.002413957,
              "nested_loop": [
                {
                  "table": {
                    "table_name": "tmall_ab__dotd_periods",
                    "access_type": "ref",
                    "possible_keys": ["PRIMARY"],
                    "key": "PRIMARY",
                    "key_length": "3",
                    "used_key_parts": ["promotion_id"],
                    "ref": ["tmall.p.promotion_id"],
                    "loops": 1,
                    "rows": 1,
                    "cost": 0.00178377,
                    "filtered": 100,
                    "attached_condition": "tmall_ab__dotd_periods.to_date >= 1765552468 and tmall_ab__dotd_periods.promotion_id = ab__dotd.promotion_id"
                  }
                }
              ]
            }
          }
        }
      },
      {
        "table": {
          "table_name": "<derived3>",
          "access_type": "ref",
          "possible_keys": ["key0"],
          "key": "key0",
          "key_length": "4",
          "used_key_parts": ["promotion_id"],
          "ref": ["tmall.ab__dotd.promotion_id"],
          "loops": 5,
          "rows": 1,
          "cost": 0.01191536,
          "filtered": 100,
          "materialized": {
            "lateral": 1,
            "query_block": {
              "select_id": 3,
              "cost": 0.002413957,
              "nested_loop": [
                {
                  "table": {
                    "table_name": "tmall_ab__dotd_periods",
                    "access_type": "ref",
                    "possible_keys": ["PRIMARY"],
                    "key": "PRIMARY",
                    "key_length": "3",
                    "used_key_parts": ["promotion_id"],
                    "ref": ["tmall.p.promotion_id"],
                    "loops": 1,
                    "rows": 1,
                    "cost": 0.00178377,
                    "filtered": 100,
                    "attached_condition": "tmall_ab__dotd_periods.to_date < 1765552468 and tmall_ab__dotd_periods.promotion_id = ab__dotd.promotion_id"
                  }
                }
              ]
            }
          }
        }
      }
    ]
  }
}