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 (
    681, 393, 41, 1827, 1454, 1476, 1473, 
    1198, 1179, 1281, 926, 1258, 1168, 1394, 
    1259, 1865, 390, 1412, 1066, 1399, 1204, 
    1387, 332, 330, 333, 329, 327, 504, 1008, 
    758, 1817, 1819, 1172, 1196, 1908, 1809, 
    1405, 1851, 730, 1481, 382, 453, 425, 
    1428, 1425, 1426, 1430, 1429
  ) 
  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.00167

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": 48,
      "filtered": 9.795915604,
      "index_condition": "cscart_product_prices.product_id in (681,393,41,1827,1454,1476,1473,1198,1179,1281,926,1258,1168,1394,1259,1865,390,1412,1066,1399,1204,1387,332,330,333,329,327,504,1008,758,1817,1819,1172,1196,1908,1809,1405,1851,730,1481,382,453,425,1428,1425,1426,1430,1429)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
41 27000.00000000
327 34000.00000000
329 40000.00000000
330 40000.00000000
332 40000.00000000
333 42000.00000000
382 25500.00000000
390 7000.00000000
393 31000.00000000
425 38000.00000000
453 50000.00000000
504 40000.00000000
681 35000.00000000
730 40000.00000000
758 46000.00000000
926 235000.00000000
1008 42000.00000000
1066 50000.00000000
1168 32000.00000000
1172 23500.00000000
1179 260000.00000000
1196 23500.00000000
1198 30000.00000000
1204 21000.00000000
1258 29000.00000000
1259 30000.00000000
1281 32000.00000000
1387 22000.00000000
1394 31000.00000000
1399 22000.00000000
1405 27000.00000000
1412 54000.00000000
1425 0.00000000
1426 0.00000000
1428 105000.00000000
1429 105000.00000000
1430 0.00000000
1454 36000.00000000
1473 35000.00000000
1476 36000.00000000
1481 24000.00000000
1809 46000.00000000
1817 42000.00000000
1819 42000.00000000
1827 84000.00000000
1851 17000.00000000
1865 45000.00000000
1908 23500.00000000