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 (
    1199, 1943, 1901, 1200, 1212, 1939, 1211, 
    1568, 1535, 1566, 1567, 1032, 1470, 
    1573, 1917, 1916, 1491, 1496, 1497, 
    1498, 1570, 1571, 1572, 1347, 665, 581, 
    299, 301, 1799, 1462, 1549, 1521, 1519, 
    1517, 1522, 1523, 1518, 1516, 1520, 
    30, 31, 1545, 1180, 1205
  ) 
  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.00166

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": 8.979589462,
      "index_condition": "cscart_product_prices.product_id in (1199,1943,1901,1200,1212,1939,1211,1568,1535,1566,1567,1032,1470,1573,1917,1916,1491,1496,1497,1498,1570,1571,1572,1347,665,581,299,301,1799,1462,1549,1521,1519,1517,1522,1523,1518,1516,1520,30,31,1545,1180,1205)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
30 37000.00000000
31 37000.00000000
299 37000.00000000
301 35000.00000000
581 35000.00000000
665 35000.00000000
1032 58000.00000000
1180 24000.00000000
1199 40000.00000000
1200 40000.00000000
1205 22000.00000000
1211 40000.00000000
1212 40000.00000000
1347 0.00000000
1462 37000.00000000
1470 66000.00000000
1491 66000.00000000
1496 66000.00000000
1497 66000.00000000
1498 66000.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
1535 18500.00000000
1545 35000.00000000
1549 27500.00000000
1566 18500.00000000
1567 18500.00000000
1568 18500.00000000
1570 26000.00000000
1571 26000.00000000
1572 26000.00000000
1573 28000.00000000
1799 37000.00000000
1901 38000.00000000
1916 28000.00000000
1917 32000.00000000
1939 44000.00000000
1943 44000.00000000