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 (
    10378, 10379, 10380, 10381, 10371, 10373, 
    10375, 10387, 10395, 10390, 10394, 
    10393, 10392, 10386, 10389, 10388, 
    10637, 10638, 10639, 10640, 10641, 
    10744, 10745, 10746, 9905, 10168, 10041, 
    10643, 10606, 10094, 10097, 12703
  ) 
  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.00057

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 (10378,10379,10380,10381,10371,10373,10375,10387,10395,10390,10394,10393,10392,10386,10389,10388,10637,10638,10639,10640,10641,10744,10745,10746,9905,10168,10041,10643,10606,10094,10097,12703)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9905 1849.73000000
10041 1849.73000000
10094 1849.73000000
10097 1849.73000000
10168 1849.73000000
10371 2763.44000000
10373 2763.44000000
10375 2763.44000000
10378 2763.44000000
10379 2763.44000000
10380 2763.44000000
10381 2763.44000000
10386 2540.59000000
10387 2540.59000000
10388 2540.59000000
10389 2540.59000000
10390 2540.59000000
10392 2540.59000000
10393 2540.59000000
10394 2540.59000000
10395 2540.59000000
10606 1849.73000000
10637 2540.59000000
10638 2540.59000000
10639 2540.59000000
10640 2540.59000000
10641 2540.59000000
10643 1849.73000000
10744 4824.88000000
10745 4824.88000000
10746 4824.88000000
12703 1849.73000000