SELECT 
  cscart_product_prices.product_id, 
  COALESCE(
    cscart_master_products_storefront_min_price.price, 
    MIN(
      IF(
        cscart_product_prices.percentage_discount = 0, 
        cscart_product_prices.price, 
        cscart_product_prices.price - (
          cscart_product_prices.price * cscart_product_prices.percentage_discount
        )/ 100
      )
    )
  ) AS price 
FROM 
  cscart_product_prices 
  LEFT JOIN cscart_master_products_storefront_min_price ON cscart_master_products_storefront_min_price.product_id = cscart_product_prices.product_id 
  AND cscart_master_products_storefront_min_price.storefront_id = 1 
WHERE 
  cscart_product_prices.product_id IN (
    8359, 8134, 8297, 8454, 18016, 8158, 
    8383, 7613, 8296, 8577, 7152, 18015, 
    8938, 7942, 7941, 7602, 7601, 7603, 
    7829, 7828, 7940, 8071, 8718, 7446
  ) 
  AND cscart_product_prices.lower_limit = 1 
  AND cscart_product_prices.usergroup_id IN (0, 1) 
GROUP BY 
  cscart_product_prices.product_id

Query time 0.00077

JSON explain

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "cscart_master_products_storefront_min_price",
      "access_type": "system",
      "possible_keys": ["PRIMARY"],
      "rows": 0,
      "filtered": 0,
      "const_row_not_found": true
    },
    "table": {
      "table_name": "cscart_product_prices",
      "access_type": "range",
      "possible_keys": ["usergroup", "product_id", "lower_limit", "usergroup_id"],
      "key": "product_id",
      "key_length": "3",
      "used_key_parts": ["product_id"],
      "rows": 24,
      "filtered": 75,
      "index_condition": "cscart_product_prices.product_id in (8359,8134,8297,8454,18016,8158,8383,7613,8296,8577,7152,18015,8938,7942,7941,7602,7601,7603,7829,7828,7940,8071,8718,7446)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
7152 35990.00000000
7446 24990.00000000
7601 14990.00000000
7602 14990.00000000
7603 14990.00000000
7613 45990.00000000
7828 17990.00000000
7829 17990.00000000
7940 17990.00000000
7941 89990.00000000
7942 54990.00000000
8071 15990.00000000
8134 54990.00000000
8158 4590.00000000
8296 45990.00000000
8297 109990.00000000
8359 54990.00000000
8383 8990.00000000
8454 119990.00000000
8577 35990.00000000
8718 15990.00000000
8938 7990.00000000
18015 0.00000000
18016 0.00000000