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 (
    1788, 1697, 1657, 1687, 1921, 1238, 1197, 
    1307, 1306, 1940, 1941, 1660, 1751, 
    1677, 1305, 1588, 1662, 1635, 1747, 
    1587, 1771, 1709, 1957, 1707, 1768, 
    1710, 1438, 1954, 1953, 1730, 1686, 
    1593, 1594, 1616, 1773, 1701, 1584, 
    1585, 1868, 1579, 1956, 1495, 1739, 
    1740, 1912, 1913, 1650, 1836
  ) 
  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": 60,
      "filtered": 13.67346573,
      "index_condition": "cscart_product_prices.product_id in (1788,1697,1657,1687,1921,1238,1197,1307,1306,1940,1941,1660,1751,1677,1305,1588,1662,1635,1747,1587,1771,1709,1957,1707,1768,1710,1438,1954,1953,1730,1686,1593,1594,1616,1773,1701,1584,1585,1868,1579,1956,1495,1739,1740,1912,1913,1650,1836)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
1197 58000.00000000
1238 40000.00000000
1305 82000.00000000
1306 0.00000000
1307 0.00000000
1438 35000.00000000
1495 60000.00000000
1579 54000.00000000
1584 56000.00000000
1585 56000.00000000
1587 0.00000000
1588 70000.00000000
1593 82000.00000000
1594 89000.00000000
1616 61000.00000000
1635 30000.00000000
1650 50000.00000000
1657 82000.00000000
1660 82000.00000000
1662 48000.00000000
1677 76000.00000000
1686 78000.00000000
1687 82000.00000000
1697 28000.00000000
1701 52000.00000000
1707 32000.00000000
1709 70000.00000000
1710 32000.00000000
1730 78000.00000000
1739 0.00000000
1740 0.00000000
1747 183000.00000000
1751 78000.00000000
1768 72000.00000000
1771 32000.00000000
1773 32000.00000000
1788 38000.00000000
1836 62000.00000000
1868 20000.00000000
1912 60000.00000000
1913 60000.00000000
1921 60000.00000000
1940 62000.00000000
1941 62000.00000000
1953 17000.00000000
1954 17000.00000000
1956 40000.00000000
1957 70000.00000000