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 (
    1836, 1336, 1837, 1820, 1304, 1738, 1893, 
    1895, 1894, 1641, 1500, 1317, 1670, 
    1658, 1669, 1558, 1644, 1262, 1552, 
    1398, 1595, 1576, 1870, 1691, 1776, 
    1785, 1633, 1563, 1564, 1667, 1786, 
    1945, 1910, 1787, 1308, 1642, 1772, 
    1736, 1277, 1672, 1325, 1665, 1778, 
    1775, 1671, 1649, 1909, 1948
  ) 
  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.00173

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": 52,
      "filtered": 9.795915604,
      "index_condition": "cscart_product_prices.product_id in (1836,1336,1837,1820,1304,1738,1893,1895,1894,1641,1500,1317,1670,1658,1669,1558,1644,1262,1552,1398,1595,1576,1870,1691,1776,1785,1633,1563,1564,1667,1786,1945,1910,1787,1308,1642,1772,1736,1277,1672,1325,1665,1778,1775,1671,1649,1909,1948)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
1262 54000.00000000
1277 315000.00000000
1304 62000.00000000
1308 70000.00000000
1317 42000.00000000
1325 130000.00000000
1336 60000.00000000
1398 75000.00000000
1500 62000.00000000
1552 25000.00000000
1558 178000.00000000
1563 26000.00000000
1564 26000.00000000
1576 25000.00000000
1595 25000.00000000
1633 26000.00000000
1641 200000.00000000
1642 44000.00000000
1644 58000.00000000
1649 18000.00000000
1658 85000.00000000
1665 70000.00000000
1667 72000.00000000
1669 0.00000000
1670 0.00000000
1671 55000.00000000
1672 55000.00000000
1691 26000.00000000
1736 215000.00000000
1738 50000.00000000
1772 32000.00000000
1775 60000.00000000
1776 60000.00000000
1778 32000.00000000
1785 40000.00000000
1786 72000.00000000
1787 28000.00000000
1820 60000.00000000
1836 62000.00000000
1837 62000.00000000
1870 70000.00000000
1893 158000.00000000
1894 158000.00000000
1895 158000.00000000
1909 60000.00000000
1910 32000.00000000
1945 70000.00000000
1948 50000.00000000