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 (
    5474, 5473, 248, 57, 205, 392, 59, 61, 
    62, 63, 65, 68, 69, 303, 73, 5556, 76, 
    380
  ) 
  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.00051

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 (5474,5473,248,57,205,392,59,61,62,63,65,68,69,303,73,5556,76,380) and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
57 6.49000000
59 6.49000000
61 6.49000000
62 6.49000000
63 11.95000000
65 6.49000000
68 8.49000000
69 6.49000000
73 6.49000000
76 6.49000000
205 6.49000000
248 8.95000000
303 6.49000000
380 6.49000000
392 8.49000000
5473 12.95000000
5474 6.49000000
5556 12.95000000