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 (
    10331, 10358, 10378, 10311, 10332, 10359, 
    10379, 10312, 10333, 10360, 10380, 
    10334, 10361, 10381, 10335, 10362, 
    10336, 10363, 10337, 10364, 10338, 
    10365, 10339, 10366, 10340, 10367, 
    10341, 10368, 10342, 12565, 10293, 
    10296
  ) 
  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.00099

JSON explain

{
  "query_block": {
    "select_id": 1,
    "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": 32,
      "filtered": 99.23484039,
      "index_condition": "cscart_product_prices.product_id in (10331,10358,10378,10311,10332,10359,10379,10312,10333,10360,10380,10334,10361,10381,10335,10362,10336,10363,10337,10364,10338,10365,10339,10366,10340,10367,10341,10368,10342,12565,10293,10296)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
10293 2206.30000000
10296 3409.73000000
10311 3225.60000000
10312 3225.60000000
10331 518.70000000
10332 518.70000000
10333 518.70000000
10334 518.70000000
10335 518.70000000
10336 518.70000000
10337 518.70000000
10338 518.70000000
10339 518.70000000
10340 518.70000000
10341 518.70000000
10342 518.70000000
10358 2121.60000000
10359 2121.60000000
10360 2121.60000000
10361 2121.60000000
10362 2121.60000000
10363 2121.60000000
10364 2121.60000000
10365 2121.60000000
10366 2121.60000000
10367 2121.60000000
10368 2121.60000000
10378 2763.44000000
10379 2763.44000000
10380 2763.44000000
10381 2763.44000000
12565 67629.15000000