SELECT 
  cscart_promotions.*, 
  cscart_promotion_descriptions.name, 
  cscart_promotion_descriptions.detailed_description, 
  cscart_promotion_descriptions.short_description 
FROM 
  cscart_promotions 
  LEFT JOIN cscart_promotion_descriptions ON cscart_promotion_descriptions.promotion_id = cscart_promotions.promotion_id 
  AND cscart_promotion_descriptions.lang_code = 'en' 
  LEFT JOIN cscart_storefronts_promotions AS storefronts_promotions ON storefronts_promotions.promotion_id = cscart_promotions.promotion_id 
WHERE 
  1 
  AND IF(
    from_date, from_date <= 1764539736, 
    1
  ) 
  AND IF(to_date, to_date >= 1764539736, 1) 
  AND status IN ('A', 'H') 
  AND cscart_promotions.zone = 'cart' 
  AND (
    storefronts_promotions.storefront_id = 1 
    OR storefronts_promotions.storefront_id IS NULL
  ) 
ORDER BY 
  cscart_promotions.stop_other_rules desc, 
  cscart_promotions.priority asc

Query time 0.00035

JSON explain

{
  "query_block": {
    "select_id": 1,
    "const_condition": "1 = 1",
    "table": {
      "table_name": "cscart_promotions",
      "access_type": "system",
      "rows": 1,
      "filtered": 100
    },
    "table": {
      "table_name": "cscart_promotion_descriptions",
      "access_type": "system",
      "possible_keys": ["PRIMARY"],
      "rows": 1,
      "filtered": 100
    },
    "table": {
      "table_name": "storefronts_promotions",
      "access_type": "system",
      "rows": 1,
      "filtered": 100
    }
  }
}

Result

promotion_id company_id conditions bonuses to_date from_date priority stop stop_other_rules zone conditions_hash status number_of_usages users_conditions_hash name detailed_description short_description
1 1 a:3:{s:3:"set";s:3:"all";s:9:"set_value";s:1:"1";s:10:"conditions";a:1:{i:1;a:3:{s:8:"operator";s:2:"eq";s:9:"condition";s:11:"coupon_code";s:5:"value";s:12:"ShippingOnly";}}} a:1:{i:1;a:4:{s:5:"bonus";s:20:"discount_on_products";s:5:"value";s:0:"";s:14:"discount_bonus";s:13:"to_percentage";s:14:"discount_value";d:0;}} 0 0 0 N N cart coupon_code=ShippingOnly A 0 Shipping Only Payment <p>Products already paid for. Used by accounting to only charge for shipping charges.</p> <p>Products already paid for. Invoice for shipping.</p>