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 (
    1557, 1556, 1555, 1554, 1553, 1551, 1549, 
    1546, 1545, 1544, 1543, 1542, 1541, 
    1540, 1539, 1538, 1537, 1512, 1534, 
    1533, 1515, 1532, 1531, 1530, 1529, 
    1528, 1527, 1526, 1508, 1525, 1524, 
    1509, 1523, 1522, 1521, 1520, 1519, 
    1518, 1517, 1516, 1277, 1494, 1507, 
    1499, 1505, 1504, 1503, 1502
  ) 
  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.00171

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 (1557,1556,1555,1554,1553,1551,1549,1546,1545,1544,1543,1542,1541,1540,1539,1538,1537,1512,1534,1533,1515,1532,1531,1530,1529,1528,1527,1526,1508,1525,1524,1509,1523,1522,1521,1520,1519,1518,1517,1516,1277,1494,1507,1499,1505,1504,1503,1502)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
1277 315000.00000000
1494 120000.00000000
1499 165000.00000000
1502 34000.00000000
1503 24000.00000000
1504 34000.00000000
1505 24000.00000000
1507 130000.00000000
1508 29000.00000000
1509 35000.00000000
1512 28000.00000000
1515 22500.00000000
1516 21000.00000000
1517 21000.00000000
1518 21000.00000000
1519 21000.00000000
1520 19000.00000000
1521 21000.00000000
1522 0.00000000
1523 21000.00000000
1524 36000.00000000
1525 28000.00000000
1526 29000.00000000
1527 29000.00000000
1528 29000.00000000
1529 28000.00000000
1530 28000.00000000
1531 28000.00000000
1532 28000.00000000
1533 28000.00000000
1534 28000.00000000
1537 30000.00000000
1538 48000.00000000
1539 32000.00000000
1540 42000.00000000
1541 0.00000000
1542 0.00000000
1543 26000.00000000
1544 28000.00000000
1545 35000.00000000
1546 10500.00000000
1549 27500.00000000
1551 70000.00000000
1553 20000.00000000
1554 20000.00000000
1555 0.00000000
1556 0.00000000
1557 68000.00000000