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 = 'uk' 
  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 <= 1745594044, 
    1
  ) 
  AND IF(to_date, to_date >= 1745594044, 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.00070

Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE cscart_promotions ALL 9 Using where; Using temporary; Using filesort
1 SIMPLE cscart_promotion_descriptions eq_ref PRIMARY PRIMARY 9 cscart_dev_4_16.cscart_promotions.promotion_id,const 1 Using where
1 SIMPLE storefronts_promotions index PRIMARY 8 6 Using where; Using index; Using join buffer (flat, BNL join)

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
25 0 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:"in";s:9:"condition";s:8:"products";s:5:"value";a:1:{i:0;a:2:{s:10:"product_id";i:12;s:6:"amount";i:3;}}}}} a:1:{i:0;a:3:{s:5:"bonus";s:14:"order_discount";s:14:"discount_bonus";s:8:"by_fixed";s:14:"discount_value";d:29.997;}} 0 0 0 N N cart products=0 A 0 Sports pants bundle
26 0 a:3:{s:3:"set";s:3:"all";s:9:"set_value";s:1:"1";s:10:"conditions";a:3:{i:1;a:3:{s:8:"operator";s:2:"in";s:9:"condition";s:8:"products";s:5:"value";a:1:{i:0;a:2:{s:10:"product_id";i:39;s:6:"amount";i:1;}}}i:2;a:3:{s:8:"operator";s:2:"in";s:9:"condition";s:8:"products";s:5:"value";a:1:{i:1;a:2:{s:10:"product_id";i:51;s:6:"amount";i:1;}}}i:3;a:3:{s:8:"operator";s:2:"in";s:9:"condition";s:8:"products";s:5:"value";a:1:{i:2;a:2:{s:10:"product_id";i:52;s:6:"amount";i:1;}}}}} a:1:{i:0;a:3:{s:5:"bonus";s:14:"order_discount";s:14:"discount_bonus";s:8:"by_fixed";s:14:"discount_value";d:369.495;}} 0 0 0 N N cart products=0;products=1;products=2 A 0 Car audio bundle
27 0 a:3:{s:3:"set";s:3:"all";s:9:"set_value";s:1:"1";s:10:"conditions";a:3:{i:1;a:3:{s:8:"operator";s:2:"in";s:9:"condition";s:8:"products";s:5:"value";a:1:{i:0;a:2:{s:10:"product_id";i:237;s:6:"amount";i:1;}}}i:2;a:3:{s:8:"operator";s:2:"in";s:9:"condition";s:8:"products";s:5:"value";a:1:{i:1;a:2:{s:10:"product_id";i:230;s:6:"amount";i:1;}}}i:3;a:3:{s:8:"operator";s:2:"in";s:9:"condition";s:8:"products";s:5:"value";a:1:{i:2;a:2:{s:10:"product_id";i:234;s:6:"amount";i:1;}}}}} a:1:{i:0;a:3:{s:5:"bonus";s:14:"order_discount";s:14:"discount_bonus";s:8:"by_fixed";s:14:"discount_value";d:160.04340000000002;}} 0 0 0 N N cart products=0;products=1;products=2 A 0 Camping bundle
24 1 a:3:{s:3:"set";s:3:"all";s:9:"set_value";s:1:"1";s:10:"conditions";a:2:{i:1;a:3:{s:8:"operator";s:3:"gte";s:9:"condition";s:8:"subtotal";s:5:"value";d:100;}i:2;a:3:{s:8:"operator";s:2:"eq";s:9:"condition";s:11:"coupon_code";s:5:"value";s:3:"123";}}} a:1:{i:1;a:2:{s:5:"bonus";s:13:"free_shipping";s:5:"value";s:1:"1";}} 0 0 1 N N cart subtotal=100;coupon_code=123 A 0 Free shipping <p>We'll ship any order above $100 for free</p>
23 1 a:3:{s:3:"set";s:3:"all";s:9:"set_value";s:1:"1";s:10:"conditions";a:1:{i:5;a:3:{s:8:"operator";s:2:"in";s:9:"condition";s:8:"products";s:5:"value";a:1:{i:1267114885;a:2:{s:10:"product_id";s:3:"248";s:6:"amount";s:1:"1";}}}}} a:1:{i:3;a:4:{s:5:"bonus";s:20:"discount_on_products";s:5:"value";s:3:"248";s:14:"discount_bonus";s:13:"by_percentage";s:14:"discount_value";d:5;}} 0 0 2 N N cart products=1267114885 A 0 X-Box discount <p>5% off the price of this gaming console</p>
15 1 a:3:{s:3:"set";s:3:"all";s:9:"set_value";s:1:"1";s:10:"conditions";a:2:{i:1;a:3:{s:8:"operator";s:3:"gte";s:9:"condition";s:8:"subtotal";s:5:"value";d:100;}i:2;a:3:{s:8:"operator";s:2:"eq";s:9:"condition";s:11:"coupon_code";s:5:"value";s:7:"sale-20";}}} a:1:{i:1;a:3:{s:5:"bonus";s:14:"order_discount";s:14:"discount_bonus";s:13:"by_percentage";s:14:"discount_value";s:2:"20";}} 0 0 3 N N cart subtotal=100;coupon_code=sale-20 A 5 20% discount coupon <p>Get 20% off your order with the bonus code "sale-20"</p>
19 1 a:3:{s:3:"set";s:3:"all";s:9:"set_value";s:1:"1";s:10:"conditions";a:2:{i:1;a:3:{s:8:"operator";s:2:"eq";s:9:"condition";s:8:"shipping";s:5:"value";s:1:"1";}i:2;a:3:{s:8:"operator";s:3:"gte";s:9:"condition";s:8:"subtotal";s:5:"value";d:100;}}} a:1:{i:1;a:3:{s:5:"bonus";s:14:"order_discount";s:14:"discount_bonus";s:8:"by_fixed";s:14:"discount_value";s:1:"8";}} 0 0 4 N N cart shipping=1;subtotal=100 A 79 Discount on pick-up <p>$8 off for collecting orders from our store</p>
18 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:8:"EMAIL-10";}}} a:1:{i:1;a:3:{s:5:"bonus";s:14:"order_discount";s:14:"discount_bonus";s:13:"by_percentage";s:14:"discount_value";s:2:"10";}} 0 0 5 N N cart coupon_code=EMAIL-10 A 0 DEMO Store discount <p>10% off our entire range of products</p>