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 <= 1732992628, 
    1
  ) 
  AND IF(to_date, to_date >= 1732992628, 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.00172

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "71.12"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "36.55"
      },
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_promotions",
            "access_type": "ALL",
            "rows_examined_per_scan": 43,
            "rows_produced_per_join": 4,
            "filtered": "10.00",
            "cost_info": {
              "read_cost": "13.74",
              "eval_cost": "0.86",
              "prefix_cost": "14.60",
              "data_read_per_join": "344"
            },
            "used_columns": [
              "promotion_id",
              "company_id",
              "conditions",
              "bonuses",
              "to_date",
              "from_date",
              "priority",
              "stop",
              "zone",
              "conditions_hash",
              "status",
              "number_of_usages",
              "users_conditions_hash",
              "stop_other_rules"
            ],
            "attached_condition": "(if(`webmarco`.`cscart_promotions`.`from_date`,(`webmarco`.`cscart_promotions`.`from_date` <= 1732992628),1) and if(`webmarco`.`cscart_promotions`.`to_date`,(`webmarco`.`cscart_promotions`.`to_date` >= 1732992628),1) and (`webmarco`.`cscart_promotions`.`status` in ('A','H')) and (`webmarco`.`cscart_promotions`.`zone` = 'cart'))"
          }
        },
        {
          "table": {
            "table_name": "cscart_promotion_descriptions",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "promotion_id",
              "lang_code"
            ],
            "key_length": "9",
            "ref": [
              "webmarco.cscart_promotions.promotion_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 4,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "4.30",
              "eval_cost": "0.86",
              "prefix_cost": "19.76",
              "data_read_per_join": "3K"
            },
            "used_columns": [
              "promotion_id",
              "name",
              "short_description",
              "detailed_description",
              "lang_code"
            ]
          }
        },
        {
          "table": {
            "table_name": "storefronts_promotions",
            "access_type": "index",
            "key": "idx_storefront_id",
            "used_key_parts": [
              "storefront_id"
            ],
            "key_length": "4",
            "rows_examined_per_scan": 40,
            "rows_produced_per_join": 36,
            "filtered": "21.25",
            "using_index": true,
            "using_join_buffer": "Block Nested Loop",
            "cost_info": {
              "read_cost": "7.50",
              "eval_cost": "7.31",
              "prefix_cost": "34.57",
              "data_read_per_join": "584"
            },
            "used_columns": [
              "storefront_id",
              "promotion_id"
            ],
            "attached_condition": "(<if>(found_match(storefronts_promotions), ((`webmarco`.`storefronts_promotions`.`storefront_id` = 1) or isnull(`webmarco`.`storefronts_promotions`.`storefront_id`)), true) and <if>(is_not_null_compl(storefronts_promotions), (`webmarco`.`storefronts_promotions`.`promotion_id` = `webmarco`.`cscart_promotions`.`promotion_id`), true))"
          }
        }
      ]
    }
  }
}

Result

promotion_id company_id conditions bonuses to_date from_date priority stop zone conditions_hash status number_of_usages users_conditions_hash stop_other_rules name detailed_description short_description
32 0 a:3:{s:3:"set";s:3:"all";s:9:"set_value";s:1:"1";s:10:"conditions";a:1:{i:9;a:3:{s:8:"operator";s:2:"eq";s:9:"condition";s:11:"coupon_code";s:5:"value";s:10:"AFFIL001WM";}}} 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:"45";}} 0 0 0 Y cart coupon_code=AFFIL001WM A 3 Y Affiliate Coupon 1
34 0 a:3:{s:3:"set";s:3:"any";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:12:{i:1537812639;a:2:{s:10:"product_id";s:6:"158795";s:6:"amount";s:1:"2";}i:622351394;a:2:{s:10:"product_id";s:6:"158798";s:6:"amount";s:1:"2";}i:525549896;a:2:{s:10:"product_id";s:6:"158802";s:6:"amount";s:1:"2";}i:2127047445;a:2:{s:10:"product_id";s:6:"158805";s:6:"amount";s:1:"2";}i:406750545;a:2:{s:10:"product_id";s:6:"158806";s:6:"amount";s:1:"2";}i:1866053063;a:2:{s:10:"product_id";s:6:"158807";s:6:"amount";s:1:"2";}i:7952298;a:2:{s:10:"product_id";s:6:"158808";s:6:"amount";s:1:"2";}i:2004772672;a:2:{s:10:"product_id";s:6:"158809";s:6:"amount";s:1:"2";}i:398061275;a:2:{s:10:"product_id";s:6:"158810";s:6:"amount";s:1:"2";}i:105410569;a:2:{s:10:"product_id";s:6:"158812";s:6:"amount";s:1:"2";}i:1901019295;a:2:{s:10:"product_id";s:6:"158813";s:6:"amount";s:1:"2";}i:1741888086;a:2:{s:10:"product_id";s:6:"158815";s:6:"amount";s:1:"2";}}}}} a:1:{i:1;a:2:{s:5:"bonus";s:13:"free_products";s:5:"value";a:3:{i:844051756;a:2:{s:10:"product_id";s:6:"158828";s:6:"amount";s:1:"1";}i:1132012061;a:2:{s:10:"product_id";s:6:"158833";s:6:"amount";s:1:"1";}i:585254978;a:2:{s:10:"product_id";s:6:"158834";s:6:"amount";s:1:"1";}}}} 0 0 0 N cart products=1537812639,622351394,525549896,2127047445,406750545,1866053063,7952298,2004772672,398061275,105410569,1901019295,1741888086 A 0 Y Aloe Creta Promotion 2+2
88 0 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:"lte";s:9:"condition";s:16:"number_of_usages";s:5:"value";i:1000000;}i:2;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:11:"coupon_code";s:5:"value";s:17:"GC-0BPN-OI6N-J9VL";}i:2;a:3:{s:8:"operator";s:3:"gte";s:9:"condition";s:8:"subtotal";s:5:"value";d:5;}}}}} 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:1:"5";}} 0 0 0 N cart number_of_usages=1000000;coupon_code=GC-0BPN-OI6N-J9VL;subtotal=5 A 0 N Gift Card
22 0 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:2:"14";}i:2;a:3:{s:8:"operator";s:2:"gt";s:9:"condition";s:8:"subtotal";s:5:"value";d:50;}}} a:1:{i:1;a:2:{s:5:"bonus";s:13:"free_shipping";s:5:"value";s:2:"14";}} 0 0 0 N cart shipping=14;subtotal=50 A 9 N Spedizione Gratuita - Joan Louis <p>- Joan Louis - Spedizione Gratuita per ordini più di 50 Euro</p> <p>- Joan Louis - Spedizione Gratuita per ordini più di 50 Euro</p>
33 0 a:3:{s:3:"set";s:3:"any";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:12:{i:1537812639;a:2:{s:10:"product_id";s:6:"158795";s:6:"amount";s:1:"1";}i:622351394;a:2:{s:10:"product_id";s:6:"158798";s:6:"amount";s:1:"1";}i:525549896;a:2:{s:10:"product_id";s:6:"158802";s:6:"amount";s:1:"1";}i:2127047445;a:2:{s:10:"product_id";s:6:"158805";s:6:"amount";s:1:"1";}i:406750545;a:2:{s:10:"product_id";s:6:"158806";s:6:"amount";s:1:"1";}i:1866053063;a:2:{s:10:"product_id";s:6:"158807";s:6:"amount";s:1:"1";}i:7952298;a:2:{s:10:"product_id";s:6:"158808";s:6:"amount";s:1:"1";}i:2004772672;a:2:{s:10:"product_id";s:6:"158809";s:6:"amount";s:1:"1";}i:398061275;a:2:{s:10:"product_id";s:6:"158810";s:6:"amount";s:1:"1";}i:105410569;a:2:{s:10:"product_id";s:6:"158812";s:6:"amount";s:1:"1";}i:1901019295;a:2:{s:10:"product_id";s:6:"158813";s:6:"amount";s:1:"1";}i:1741888086;a:2:{s:10:"product_id";s:6:"158815";s:6:"amount";s:1:"1";}}}}} a:1:{i:1;a:2:{s:5:"bonus";s:13:"free_products";s:5:"value";a:1:{i:844051756;a:2:{s:10:"product_id";s:6:"158828";s:6:"amount";s:1:"1";}}}} 0 0 0 N cart products=1537812639,622351394,525549896,2127047445,406750545,1866053063,7952298,2004772672,398061275,105410569,1901019295,1741888086 A 0 N Aloe Creta Promotion 1+1
36 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:42:{i:68838275;a:2:{s:10:"product_id";s:6:"225392";s:6:"amount";s:1:"1";}i:1702955488;a:2:{s:10:"product_id";s:6:"225395";s:6:"amount";s:1:"1";}i:58173338;a:2:{s:10:"product_id";s:6:"225396";s:6:"amount";s:1:"1";}i:1953535756;a:2:{s:10:"product_id";s:6:"225397";s:6:"amount";s:1:"1";}i:456160611;a:2:{s:10:"product_id";s:6:"225398";s:6:"amount";s:1:"1";}i:1815561717;a:2:{s:10:"product_id";s:6:"225399";s:6:"amount";s:1:"1";}i:1050259299;a:2:{s:10:"product_id";s:6:"225400";s:6:"amount";s:1:"1";}i:972319610;a:2:{s:10:"product_id";s:6:"225404";s:6:"amount";s:1:"1";}i:1324571628;a:2:{s:10:"product_id";s:6:"225405";s:6:"amount";s:1:"1";}i:671479210;a:2:{s:10:"product_id";s:6:"225406";s:6:"amount";s:1:"1";}i:1594017088;a:2:{s:10:"product_id";s:6:"225407";s:6:"amount";s:1:"1";}i:809641809;a:2:{s:10:"product_id";s:6:"225408";s:6:"amount";s:1:"1";}i:1195710407;a:2:{s:10:"product_id";s:6:"225409";s:6:"amount";s:1:"1";}i:662871586;a:2:{s:10:"product_id";s:6:"225410";s:6:"amount";s:1:"1";}i:1350938292;a:2:{s:10:"product_id";s:6:"225411";s:6:"amount";s:1:"1";}i:913506546;a:2:{s:10:"product_id";s:6:"225412";s:6:"amount";s:1:"1";}i:1098133608;a:2:{s:10:"product_id";s:6:"225413";s:6:"amount";s:1:"1";}i:552557115;a:2:{s:10:"product_id";s:6:"225414";s:6:"amount";s:1:"1";}i:1474849453;a:2:{s:10:"product_id";s:6:"225415";s:6:"amount";s:1:"1";}i:824100073;a:2:{s:10:"product_id";s:6:"225416";s:6:"amount";s:1:"1";}i:1176106111;a:2:{s:10:"product_id";s:6:"225417";s:6:"amount";s:1:"1";}i:693704208;a:2:{s:10:"product_id";s:6:"225418";s:6:"amount";s:1:"1";}i:1583228550;a:2:{s:10:"product_id";s:6:"225419";s:6:"amount";s:1:"1";}i:212848097;a:2:{s:10:"product_id";s:6:"225420";s:6:"amount";s:1:"1";}i:2074672503;a:2:{s:10:"product_id";s:6:"225421";s:6:"amount";s:1:"1";}i:492720947;a:2:{s:10:"product_id";s:6:"225422";s:6:"amount";s:1:"1";}i:1784243109;a:2:{s:10:"product_id";s:6:"225423";s:6:"amount";s:1:"1";}i:197265912;a:2:{s:10:"product_id";s:6:"225424";s:6:"amount";s:1:"1";}i:2093300078;a:2:{s:10:"product_id";s:6:"225425";s:6:"amount";s:1:"1";}i:439588652;a:2:{s:10:"product_id";s:6:"225426";s:6:"amount";s:1:"1";}i:1832167358;a:2:{s:10:"product_id";s:6:"225427";s:6:"amount";s:1:"1";}i:1970500933;a:2:{s:10:"product_id";s:6:"225429";s:6:"amount";s:1:"1";}i:364182688;a:2:{s:10:"product_id";s:6:"225430";s:6:"amount";s:1:"1";}i:1655950390;a:2:{s:10:"product_id";s:6:"225431";s:6:"amount";s:1:"1";}i:71656052;a:2:{s:10:"product_id";s:6:"225432";s:6:"amount";s:1:"1";}i:1933726438;a:2:{s:10:"product_id";s:6:"225433";s:6:"amount";s:1:"1";}i:316225721;a:2:{s:10:"product_id";s:6:"225434";s:6:"amount";s:1:"1";}i:1709049903;a:2:{s:10:"product_id";s:6:"225435";s:6:"amount";s:1:"1";}i:52995691;a:2:{s:10:"product_id";s:6:"225436";s:6:"amount";s:1:"1";}i:1949275901;a:2:{s:10:"product_id";s:6:"225437";s:6:"amount";s:1:"1";}i:460289170;a:2:{s:10:"product_id";s:6:"225438";s:6:"amount";s:1:"1";}i:1818772484;a:2:{s:10:"product_id";s:6:"225439";s:6:"amount";s:1:"1";}}}}} 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 0 N cart products=68838275,1702955488,58173338,1953535756,456160611,1815561717,1050259299,972319610,1324571628,671479210,1594017088,809641809,1195710407,662871586,1350938292,913506546,1098133608,552557115,1474849453,824100073,1176106111,693704208,1583228550,212848097,2074672503,492720947,1784243109,197265912,2093300078,439588652,1832167358,1970500933,364182688,1655950390,71656052,1933726438,316225721,1709049903,52995691,1949275901,460289170,1818772484 A 0 N Promotion 20% OFF - KNITTING DESIGN TINA STYLE <p><strong>&nbsp;20% discount for products over 100€</strong></p>
83 0 a:3:{s:3:"set";s:3:"all";s:9:"set_value";s:1:"1";s:10:"conditions";a:1:{i:2;a:3:{s:8:"operator";s:3:"gte";s:9:"condition";s:15:"products_number";s:5:"value";d:12;}}} a:1:{i:1;a:2:{s:5:"bonus";s:13:"free_shipping";s:5:"value";s:3:"209";}} 0 0 0 N cart products_number=12 A 1 N The comfort Promo
89 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: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:"lte";s:9:"condition";s:16:"number_of_usages";s:5:"value";i:10000;}i:2;a:3:{s:8:"operator";s:2:"eq";s:9:"condition";s:11:"coupon_code";s:5:"value";s:17:"GC-UUIN-ZXJX-PDEZ";}}}}} a:1:{i:1;a:3:{s:5:"bonus";s:14:"order_discount";s:14:"discount_bonus";s:13:"to_percentage";s:14:"discount_value";s:2:"10";}} 0 0 0 N cart number_of_usages=10000;coupon_code=GC-UUIN-ZXJX-PDEZ A 0 N Gift Card 2
19 0 a:3:{s:3:"set";s:3:"all";s:9:"set_value";s:1:"1";s:10:"conditions";a:16:{i:1;a:3:{s:8:"operator";s:2:"eq";s:9:"condition";s:8:"shipping";s:5:"value";s:2:"13";}i:2;a:3:{s:8:"operator";s:2:"gt";s:9:"condition";s:8:"subtotal";s:5:"value";d:80;}i:3;a:3:{s:8:"operator";s:3:"neq";s:9:"condition";s:5:"state";s:5:"value";s:2:"NU";}i:4;a:3:{s:8:"operator";s:3:"neq";s:9:"condition";s:5:"state";s:5:"value";s:2:"SS";}i:5;a:3:{s:8:"operator";s:3:"neq";s:9:"condition";s:5:"state";s:5:"value";s:2:"OR";}i:6;a:3:{s:8:"operator";s:3:"neq";s:9:"condition";s:5:"state";s:5:"value";s:2:"CA";}i:7;a:3:{s:8:"operator";s:3:"neq";s:9:"condition";s:5:"state";s:5:"value";s:2:"AG";}i:8;a:3:{s:8:"operator";s:3:"neq";s:9:"condition";s:5:"state";s:5:"value";s:2:"CL";}i:9;a:3:{s:8:"operator";s:3:"neq";s:9:"condition";s:5:"state";s:5:"value";s:2:"CT";}i:10;a:3:{s:8:"operator";s:3:"neq";s:9:"condition";s:5:"state";s:5:"value";s:2:"EN";}i:11;a:3:{s:8:"operator";s:3:"neq";s:9:"condition";s:5:"state";s:5:"value";s:2:"ME";}i:12;a:3:{s:8:"operator";s:3:"neq";s:9:"condition";s:5:"state";s:5:"value";s:2:"PA";}i:13;a:3:{s:8:"operator";s:3:"neq";s:9:"condition";s:5:"state";s:5:"value";s:2:"RG";}i:14;a:3:{s:8:"operator";s:3:"neq";s:9:"condition";s:5:"state";s:5:"value";s:2:"SR";}i:15;a:3:{s:8:"operator";s:3:"neq";s:9:"condition";s:5:"state";s:5:"value";s:2:"TP";}i:16;a:3:{s:8:"operator";s:3:"neq";s:9:"condition";s:5:"state";s:5:"value";s:2:"SU";}}} a:1:{i:1;a:2:{s:5:"bonus";s:13:"free_shipping";s:5:"value";s:2:"13";}} 0 0 1 N cart shipping=13;subtotal=80;state=NU;state=SS;state=OR;state=CA;state=AG;state=CL;state=CT;state=EN;state=ME;state=PA;state=RG;state=SR;state=TP;state=SU H 20 N Free Shipping ZG
20 0 a:3:{s:3:"set";s:3:"all";s:9:"set_value";s:1:"1";s:10:"conditions";a:3:{i:2;a:3:{s:3:"set";s:3:"any";s:9:"set_value";s:1:"1";s:10:"conditions";a:15:{i:1;a:3:{s:8:"operator";s:2:"in";s:9:"condition";s:5:"state";s:5:"value";s:2:"SS";}i:2;a:3:{s:8:"operator";s:2:"in";s:9:"condition";s:5:"state";s:5:"value";s:2:"NU";}i:3;a:3:{s:8:"operator";s:2:"in";s:9:"condition";s:5:"state";s:5:"value";s:2:"OR";}i:4;a:3:{s:8:"operator";s:2:"in";s:9:"condition";s:5:"state";s:5:"value";s:2:"CA";}i:5;a:3:{s:8:"operator";s:2:"in";s:9:"condition";s:5:"state";s:5:"value";s:2:"AG";}i:6;a:3:{s:8:"operator";s:2:"in";s:9:"condition";s:5:"state";s:5:"value";s:2:"CL";}i:7;a:3:{s:8:"operator";s:2:"in";s:9:"condition";s:5:"state";s:5:"value";s:2:"CT";}i:8;a:3:{s:8:"operator";s:2:"in";s:9:"condition";s:5:"state";s:5:"value";s:2:"EN";}i:9;a:3:{s:8:"operator";s:2:"in";s:9:"condition";s:5:"state";s:5:"value";s:2:"ME";}i:10;a:3:{s:8:"operator";s:2:"in";s:9:"condition";s:5:"state";s:5:"value";s:2:"PA";}i:11;a:3:{s:8:"operator";s:2:"in";s:9:"condition";s:5:"state";s:5:"value";s:2:"RG";}i:12;a:3:{s:8:"operator";s:2:"in";s:9:"condition";s:5:"state";s:5:"value";s:2:"SR";}i:13;a:3:{s:8:"operator";s:2:"in";s:9:"condition";s:5:"state";s:5:"value";s:2:"TP";}i:14;a:3:{s:8:"operator";s:2:"in";s:9:"condition";s:5:"state";s:5:"value";s:2:"SU";}i:15;a:3:{s:8:"operator";s:4:"cont";s:9:"condition";s:15:"zip_postal_code";s:5:"value";s:5:"30010";}}}i:3;a:3:{s:8:"operator";s:2:"eq";s:9:"condition";s:8:"shipping";s:5:"value";s:2:"13";}i:4;a:3:{s:8:"operator";s:2:"gt";s:9:"condition";s:8:"subtotal";s:5:"value";d:100;}}} a:1:{i:1;a:2:{s:5:"bonus";s:13:"free_shipping";s:5:"value";s:2:"13";}} 0 0 2 Y cart state=SS;state=NU;state=OR;state=CA;state=AG;state=CL;state=CT;state=EN;state=ME;state=PA;state=RG;state=SR;state=TP;state=SU;zip_postal_code=30010;shipping=13;subtotal=100 H 2 N Free Shipping in Italian Minor Islands <p>Free Shipping in Italian Minor Islands for orders over 100 Euro.</p> <p>Free Shipping in&nbsp;Italian Minor Islands&nbsp;for orders over 100 Euro.</p>