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 (
    4843, 4835, 4824, 4438, 5073, 4828, 4359, 
    4360, 4422, 4440, 4446, 4629, 4625, 
    4886, 4485, 4768, 4781, 4829, 4784, 
    18144, 4785, 5348, 4970, 4802
  ) 
  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.00085

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 (4843,4835,4824,4438,5073,4828,4359,4360,4422,4440,4446,4629,4625,4886,4485,4768,4781,4829,4784,18144,4785,5348,4970,4802)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
4359 89990.00000000
4360 89990.00000000
4422 99990.00000000
4438 31990.00000000
4440 49990.00000000
4446 49990.00000000
4485 11990.00000000
4625 87990.00000000
4629 87990.00000000
4768 13990.00000000
4781 13990.00000000
4784 41990.00000000
4785 55990.00000000
4802 15990.00000000
4824 44990.00000000
4828 34990.00000000
4829 49990.00000000
4835 36990.00000000
4843 44990.00000000
4886 125990.00000000
4970 15990.00000000
5073 34990.00000000
5348 12990.00000000
18144 39990.00000000