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, 232, 54, 337, 5473, 315, 236, 3478, 
    341, 5475, 59, 309, 65, 69, 70, 75, 5556, 
    76
  ) 
  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,232,54,337,5473,315,236,3478,341,5475,59,309,65,69,70,75,5556,76) and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
54 6.49000000
59 6.49000000
65 6.49000000
69 6.49000000
70 6.49000000
75 6.49000000
76 6.49000000
232 7.50000000
236 6.49000000
298 6.95000000
309 6.99000000
315 15.95000000
337 16.95000000
341 30.95000000
3478 9.95000000
5473 12.95000000
5475 4.95000000
5556 12.95000000