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 (
    1501, 1487, 1498, 1497, 1496, 1491, 1495, 
    1493, 1486, 1489, 1488, 1485, 1459, 
    1477, 1484, 1483, 1482, 1480, 1481, 
    1474, 1479, 1478, 1465, 1475, 1476, 
    1473, 1276, 1463, 1471, 1462, 1267, 
    1460, 820, 1472, 1464, 1470, 1469, 1468, 
    1467, 1466, 1461, 1458, 1457, 1456, 
    1455, 1454, 1446, 1450
  ) 
  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 (1501,1487,1498,1497,1496,1491,1495,1493,1486,1489,1488,1485,1459,1477,1484,1483,1482,1480,1481,1474,1479,1478,1465,1475,1476,1473,1276,1463,1471,1462,1267,1460,820,1472,1464,1470,1469,1468,1467,1466,1461,1458,1457,1456,1455,1454,1446,1450)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
820 98000.00000000
1267 48000.00000000
1276 0.00000000
1446 10000.00000000
1450 47000.00000000
1454 36000.00000000
1455 47000.00000000
1456 22000.00000000
1457 0.00000000
1458 45000.00000000
1459 0.00000000
1460 46000.00000000
1461 22000.00000000
1462 37000.00000000
1463 0.00000000
1464 0.00000000
1465 38000.00000000
1466 42000.00000000
1467 36000.00000000
1468 36000.00000000
1469 28000.00000000
1470 66000.00000000
1471 78000.00000000
1472 145000.00000000
1473 35000.00000000
1474 112000.00000000
1475 0.00000000
1476 36000.00000000
1477 100000.00000000
1478 44000.00000000
1479 42000.00000000
1480 34000.00000000
1481 24000.00000000
1482 36000.00000000
1483 34000.00000000
1484 36000.00000000
1485 22000.00000000
1486 118000.00000000
1487 58000.00000000
1488 0.00000000
1489 0.00000000
1491 66000.00000000
1493 260000.00000000
1495 60000.00000000
1496 66000.00000000
1497 66000.00000000
1498 66000.00000000
1501 34000.00000000