SELECT 
  vi_promotions.*, 
  vi_promotion_descriptions.name, 
  vi_promotion_descriptions.detailed_description, 
  vi_promotion_descriptions.short_description, 
  vi_promotion_images.promotion_image_id 
FROM 
  vi_promotions 
  LEFT JOIN vi_promotion_descriptions ON vi_promotion_descriptions.promotion_id = vi_promotions.promotion_id 
  AND vi_promotion_descriptions.lang_code = 'en' 
  LEFT JOIN vi_storefronts_promotions AS storefronts_promotions ON storefronts_promotions.promotion_id = vi_promotions.promotion_id 
  LEFT JOIN vi_promotion_images ON vi_promotion_images.promotion_id = vi_promotions.promotion_id 
  AND vi_promotion_images.lang_code = 'en' 
WHERE 
  1 
  AND status IN ('A') 
  AND (
    storefronts_promotions.storefront_id = 1 
    OR storefronts_promotions.storefront_id IS NULL
  ) 
  AND vi_promotions.promotion_id NOT IN (25, 26, 27) 
ORDER BY 
  (
    IF(from_date > 1776419695, 1, 0) + IF(
      to_date 
      AND to_date < 1776419695, 
      2, 
      0
    )
  ) asc, 
  ABS(
    (
      IF(
        from_date > 1776419695, from_date, 
        to_date
      )/-1
    ) + 1776419695
  ) asc 
LIMIT 
  0, 12

Query time 0.00081

JSON explain

{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "if(vi_promotions.from_date > 1776419695,1,0) + if(vi_promotions.to_date <> 0 and vi_promotions.to_date < 1776419695,2,0), abs(if(vi_promotions.from_date > 1776419695,vi_promotions.from_date,vi_promotions.to_date) / -1 + 1776419695)",
      "temporary_table": {
        "nested_loop": [
          {
            "table": {
              "table_name": "vi_promotions",
              "access_type": "ALL",
              "possible_keys": ["PRIMARY"],
              "rows": 14,
              "filtered": 100,
              "attached_condition": "vi_promotions.`status` = 'A' and vi_promotions.promotion_id not in (25,26,27)"
            }
          },
          {
            "table": {
              "table_name": "vi_promotion_descriptions",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY"],
              "key": "PRIMARY",
              "key_length": "9",
              "used_key_parts": ["promotion_id", "lang_code"],
              "ref": ["devvipiriscom.vi_promotions.promotion_id", "const"],
              "rows": 1,
              "filtered": 100,
              "attached_condition": "trigcond(vi_promotion_descriptions.lang_code = 'en')"
            }
          },
          {
            "block-nl-join": {
              "table": {
                "table_name": "storefronts_promotions",
                "access_type": "index",
                "key": "PRIMARY",
                "key_length": "8",
                "used_key_parts": ["storefront_id", "promotion_id"],
                "rows": 11,
                "filtered": 100,
                "using_index": true
              },
              "buffer_type": "flat",
              "buffer_size": "22Kb",
              "join_type": "BNL",
              "attached_condition": "trigcond(storefronts_promotions.storefront_id = 1 or storefronts_promotions.storefront_id is null) and trigcond(storefronts_promotions.promotion_id = vi_promotions.promotion_id)"
            }
          },
          {
            "table": {
              "table_name": "vi_promotion_images",
              "access_type": "eq_ref",
              "possible_keys": ["promo"],
              "key": "promo",
              "key_length": "10",
              "used_key_parts": ["promotion_id", "lang_code"],
              "ref": ["devvipiriscom.vi_promotions.promotion_id", "const"],
              "rows": 1,
              "filtered": 100,
              "attached_condition": "trigcond(vi_promotion_images.promotion_id = vi_promotions.promotion_id and vi_promotion_images.lang_code = 'en')"
            }
          }
        ]
      }
    }
  }
}

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 promotion_image_id
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> 6
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> 7
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 1 Discount on pick-up <p>$8 off for collecting orders from our store</p> 8
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> 9
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> 10
28 1 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:2:"in";s:9:"condition";s:8:"products";s:5:"value";s:19:"247,214,217,218,244";}}} a:1:{i:1;a:3:{s:5:"bonus";s:16:"product_discount";s:14:"discount_bonus";s:13:"by_percentage";s:14:"discount_value";s:2:"20";}} 1774631692 0 0 N N catalog products=247,214,217,218,244 A 0 AB: Stock! Buy a promotional hair dryer VALERA - get a 40% discount on the curling iron! <p>Every buyer of a promotional hair dryer VALERA can get a 40% discount on a cooler *! To receive a gift, you need to add a VALERA promotional hairdryer and an action souplink to the cart.</p><p>The promotion is valid only, when placing an order on the site.</p><p>Hurry, the number of gifts is limited!</p><p>* 40% of the discount is given on the promotional product - promotional coupon.<br></p> <p>To receive a gift, you need to add a VALERA promotional hairdryer and an action souplink to the basket.</p><p>The promotion is valid only when placing an order on the site.<br></p> 47
29 1 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:2:"in";s:9:"condition";s:8:"products";s:5:"value";s:19:"219,220,221,222,227";}}} a:1:{i:1;a:3:{s:5:"bonus";s:16:"product_discount";s:14:"discount_bonus";s:13:"by_percentage";s:14:"discount_value";s:2:"50";}} 1774631692 0 0 N N catalog products=219,220,221,222,227 A 0 AB: RACING CARD + 2-YEAR LICENSE FOR RACES K1 <p><span class="legal-disclosures-line1">Promotional value expires 60 days after purchase. Amount paid never expires.</span> <span class="fine-print-description">Limit 2 per person, may buy 20 additional as gifts. Must activate licenses by the expiration date. Licenses expire 1 year from activation date. All junior racers must be at least 48'' and adult racers must be at least 4'10'' tall to race. All racers should be under 7' tall and 300 lbs. Subject to availability. Not valid for group events. May split between 2 people.</span> <span class="legal-disclosures-line2">Merchant is solely responsible to purchasers for the care and quality of the advertised goods and services.</span></p> <p><span class="legal-disclosures-line2"><span class="legal-disclosures-line1">Promotional value expires 60 days after purchase. Amount paid never expires.</span> <span class="fine-print-description">Limit 2 per person, may buy 20 additional as gifts.</span></span></p> 56
30 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:"in";s:9:"condition";s:8:"products";s:5:"value";s:19:"228,229,205,206,207";}}} a:1:{i:1;a:3:{s:5:"bonus";s:16:"product_discount";s:14:"discount_bonus";s:13:"by_percentage";s:14:"discount_value";s:2:"10";}} 1774631692 0 0 N N catalog products=228,229,205,206,207 A 0 AB: Only today you can buy a camera with - 10% discount <p>Buy a camera of a well-known brand with 10% discount during this period. </p><p>Buying promotional models of cameras or Canon lenses, you get a free one-year subscription to a plan for photographers from Adobe Creative Cloud and one of the online courses.<br></p> <p>Buy a camera of a well-known brand with 10% discount during this period. .<br> </p> 65
31 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:3:"gte";s:9:"condition";s:8:"subtotal";s:5:"value";d:3000;}}} a:1:{i:0;a:2:{s:5:"bonus";s:13:"free_shipping";s:5:"value";s:1:"1";}} 1774631692 0 0 N N cart subtotal=3000 A 0 AB: Free shipping for orders over $ 50. <p>Our online store offers the best delivery terms for orders over $ 50. Take advantage of this offer right now and save on delivery, we will bring products at our own expense.</p> <p>Get exclusive shipping terms for orders over $ 50.</p> 74
32 1 a:3:{s:3:"set";s:3:"all";s:9:"set_value";s:1:"1";s:10:"conditions";a:1:{i:6;a:3:{s:8:"operator";s:2:"in";s:9:"condition";s:10:"categories";s:5:"value";s:3:"168";}}} a:1:{i:2;a:4:{s:5:"bonus";s:20:"discount_on_products";s:5:"value";s:19:"223,224,225,240,241";s:14:"discount_bonus";s:13:"by_percentage";s:14:"discount_value";d:5;}} 1774631692 0 4 N N cart categories=168 A 0 AB: 5% discount for X-Box One & Sabertooth bundle <p>Buying X-Box One & Sabertooth in a bundle, you will get a 5% discount for all order. Harry up, our offer is limited (only the first 500 purchases will receive such exclusive conditions) </p> <p>Buying X-Box One & Sabertooth in a bundle, you will get a 5% discount for all order. </p> 83
14 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:"in";s:9:"condition";s:10:"categories";s:5:"value";s:3:"243";}}} a:1:{i:1;a:3:{s:5:"bonus";s:16:"product_discount";s:14:"discount_bonus";s:13:"to_percentage";s:14:"discount_value";s:2:"95";}} 1419451199 1411502400 0 N N catalog categories=243 A 3 DVD 5% off