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 (
    1561, 1705, 1704, 1609, 1935, 1936, 1929, 
    1528, 1527, 1526, 1508, 1534, 1533, 
    1841, 1531, 1529, 1530, 1843, 1532, 
    1515, 1904, 1873, 1897, 1896, 1728, 
    1607, 1608, 1606, 1703, 1578, 1396, 
    1605, 1206, 1586, 1934, 1932, 1933, 
    1656, 1542, 1640, 1543, 1655, 1927, 
    1926, 1815, 1808, 1541, 1829
  ) 
  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.00175

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": 64,
      "filtered": 9.795915604,
      "index_condition": "cscart_product_prices.product_id in (1561,1705,1704,1609,1935,1936,1929,1528,1527,1526,1508,1534,1533,1841,1531,1529,1530,1843,1532,1515,1904,1873,1897,1896,1728,1607,1608,1606,1703,1578,1396,1605,1206,1586,1934,1932,1933,1656,1542,1640,1543,1655,1927,1926,1815,1808,1541,1829)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
1206 34000.00000000
1396 160000.00000000
1508 29000.00000000
1515 22500.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
1541 0.00000000
1542 0.00000000
1543 26000.00000000
1561 18000.00000000
1578 38000.00000000
1586 11000.00000000
1605 26000.00000000
1606 22000.00000000
1607 22000.00000000
1608 22000.00000000
1609 34000.00000000
1640 26000.00000000
1655 40000.00000000
1656 40000.00000000
1703 76000.00000000
1704 50000.00000000
1705 50000.00000000
1728 28000.00000000
1808 25000.00000000
1815 25000.00000000
1829 36000.00000000
1841 25000.00000000
1843 28000.00000000
1873 63000.00000000
1896 63000.00000000
1897 63000.00000000
1904 26000.00000000
1926 23000.00000000
1927 0.00000000
1929 28000.00000000
1932 23000.00000000
1933 23000.00000000
1934 23000.00000000
1935 22500.00000000
1936 23000.00000000