SELECT 
  cscart_product_prices.product_id, 
  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 
WHERE 
  cscart_product_prices.product_id IN (
    298, 488, 52, 53, 54, 222, 55, 5474, 5473, 
    345, 56, 248, 57, 205, 518, 236, 392, 
    58
  ) 
  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.00062

JSON explain

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "cscart_product_prices",
      "access_type": "range",
      "possible_keys": ["product_id"],
      "key": "product_id",
      "key_length": "9",
      "used_key_parts": ["product_id", "lower_limit", "usergroup_id"],
      "rows": 36,
      "filtered": 100,
      "index_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.product_id in (298,488,52,53,54,222,55,5474,5473,345,56,248,57,205,518,236,392,58) and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
52 8.49000000
53 8.49000000
54 6.49000000
55 6.49000000
56 6.49000000
57 6.49000000
58 8.49000000
205 6.49000000
222 6.49000000
236 6.49000000
248 8.95000000
298 6.95000000
345 6.49000000
392 8.49000000
488 6.49000000
518 3.49000000
5473 12.95000000
5474 6.49000000