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 (
    4434, 4469, 4470, 4216, 4919, 4729, 18149, 
    4920, 4479, 4489, 4480, 4770, 5084, 
    5085, 4924, 5315, 5446, 5167, 5131, 
    4488, 4936, 5309, 4942, 4943
  ) 
  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.00792

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 (4434,4469,4470,4216,4919,4729,18149,4920,4479,4489,4480,4770,5084,5085,4924,5315,5446,5167,5131,4488,4936,5309,4942,4943)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
4216 14990.00000000
4434 15990.00000000
4469 12990.00000000
4470 12990.00000000
4479 6990.00000000
4480 8490.00000000
4488 42990.00000000
4489 8490.00000000
4729 20990.00000000
4770 10990.00000000
4919 14990.00000000
4920 6990.00000000
4924 42990.00000000
4936 57990.00000000
4942 57990.00000000
4943 57990.00000000
5084 2490.00000000
5085 2490.00000000
5131 42990.00000000
5167 47990.00000000
5309 57990.00000000
5315 42990.00000000
5446 37990.00000000
18149 25990.00000000