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 (
    1562, 1509, 1952, 1700, 1589, 1492, 1316, 
    1690, 1729, 1637, 1731, 1636, 1638, 
    1639, 1629, 1631, 1630, 1622, 1615, 
    1621, 1907, 1955, 1663, 1674, 1490, 
    1746, 1831, 1832, 1591, 1737, 1805, 
    1647, 1774, 281, 1617, 282, 1503, 285, 
    1826, 1646, 1580, 1906, 1611, 1166, 
    67, 1252, 1930, 1561
  ) 
  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.00176

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": 54,
      "filtered": 9.795915604,
      "index_condition": "cscart_product_prices.product_id in (1562,1509,1952,1700,1589,1492,1316,1690,1729,1637,1731,1636,1638,1639,1629,1631,1630,1622,1615,1621,1907,1955,1663,1674,1490,1746,1831,1832,1591,1737,1805,1647,1774,281,1617,282,1503,285,1826,1646,1580,1906,1611,1166,67,1252,1930,1561)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
67 48000.00000000
281 66000.00000000
282 70000.00000000
285 66000.00000000
1166 0.00000000
1252 37000.00000000
1316 22000.00000000
1490 80000.00000000
1492 165000.00000000
1503 24000.00000000
1509 35000.00000000
1561 18000.00000000
1562 35000.00000000
1580 46000.00000000
1589 73000.00000000
1591 85000.00000000
1611 50000.00000000
1615 78000.00000000
1617 70000.00000000
1621 78000.00000000
1622 80000.00000000
1629 80000.00000000
1630 80000.00000000
1631 80000.00000000
1636 39000.00000000
1637 72000.00000000
1638 39000.00000000
1639 65000.00000000
1646 56000.00000000
1647 65000.00000000
1663 92000.00000000
1674 80000.00000000
1690 52000.00000000
1700 60000.00000000
1729 68000.00000000
1731 0.00000000
1737 0.00000000
1746 82000.00000000
1774 34000.00000000
1805 72000.00000000
1826 72000.00000000
1831 32000.00000000
1832 32000.00000000
1906 58000.00000000
1907 80000.00000000
1930 23000.00000000
1952 165000.00000000
1955 95000.00000000