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, 53, 232, 55, 337, 205, 3478, 392, 
    66, 69, 5556, 89, 5477, 150, 475, 234, 
    139
  ) 
  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.00047

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": 34,
      "filtered": 100,
      "index_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.product_id in (298,53,232,55,337,205,3478,392,66,69,5556,89,5477,150,475,234,139) and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
53 8.49000000
55 6.49000000
66 6.49000000
69 6.49000000
89 6.49000000
139 6.49000000
150 6.49000000
205 6.49000000
232 7.50000000
234 6.49000000
298 6.95000000
337 16.95000000
392 8.49000000
475 49.95000000
3478 9.95000000
5477 8.95000000
5556 12.95000000