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 (
    710, 706, 1760, 1844, 1689, 1754, 1755, 
    1840, 1756, 1753, 1792, 1791, 1793, 
    1789, 1472, 1716, 1714, 1715, 1720, 
    1719, 1722, 1713, 1718, 1717, 1867, 
    1721, 1145, 1891, 1202, 247, 296, 1218, 
    1765, 820, 1816, 1651, 1661, 255, 65, 
    1959, 1011, 1685, 1207, 1210, 1203, 
    1389, 1188, 1190, 1209, 1186, 114, 1810, 
    113, 1742, 1598, 1565, 1597, 1708, 1694, 
    1732, 1596, 1632, 79, 297, 1852, 1456, 
    653, 1465, 1494, 1620, 1619, 1457, 1618, 
    1623, 1624, 1395, 1614, 1604, 1801, 
    1802, 1724, 1727, 1726, 1599, 1725, 
    1600, 1872, 1603, 1560, 1513, 1550, 
    1548, 1547, 1763, 1764, 1506
  ) 
  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.00311

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": 96,
      "filtered": 20.51019859,
      "index_condition": "cscart_product_prices.product_id in (710,706,1760,1844,1689,1754,1755,1840,1756,1753,1792,1791,1793,1789,1472,1716,1714,1715,1720,1719,1722,1713,1718,1717,1867,1721,1145,1891,1202,247,296,1218,1765,820,1816,1651,1661,255,65,1959,1011,1685,1207,1210,1203,1389,1188,1190,1209,1186,114,1810,113,1742,1598,1565,1597,1708,1694,1732,1596,1632,79,297,1852,1456,653,1465,1494,1620,1619,1457,1618,1623,1624,1395,1614,1604,1801,1802,1724,1727,1726,1599,1725,1600,1872,1603,1560,1513,1550,1548,1547,1763,1764,1506)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
65 68000.00000000
79 60000.00000000
113 485000.00000000
114 380000.00000000
247 42000.00000000
255 66000.00000000
296 32000.00000000
297 60000.00000000
653 144000.00000000
706 105000.00000000
710 110000.00000000
820 98000.00000000
1011 106000.00000000
1145 29000.00000000
1186 21000.00000000
1188 14500.00000000
1190 21000.00000000
1202 46000.00000000
1203 12500.00000000
1207 12500.00000000
1209 21000.00000000
1210 12500.00000000
1218 335000.00000000
1389 12500.00000000
1395 0.00000000
1456 22000.00000000
1457 0.00000000
1465 38000.00000000
1472 145000.00000000
1494 120000.00000000
1506 24500.00000000
1513 27500.00000000
1547 27500.00000000
1548 27500.00000000
1550 27500.00000000
1560 0.00000000
1565 22000.00000000
1596 128000.00000000
1597 23000.00000000
1598 23000.00000000
1599 22000.00000000
1600 24000.00000000
1603 52000.00000000
1604 70000.00000000
1614 70000.00000000
1618 0.00000000
1619 84000.00000000
1620 115000.00000000
1623 0.00000000
1624 0.00000000
1632 56000.00000000
1651 58000.00000000
1661 66000.00000000
1685 200000.00000000
1689 106000.00000000
1694 0.00000000
1708 70000.00000000
1713 0.00000000
1714 36000.00000000
1715 37000.00000000
1716 36000.00000000
1717 36000.00000000
1718 37000.00000000
1719 36000.00000000
1720 36000.00000000
1721 0.00000000
1722 0.00000000
1724 22000.00000000
1725 22000.00000000
1726 22000.00000000
1727 22000.00000000
1732 30000.00000000
1742 32000.00000000
1753 110000.00000000
1754 113000.00000000
1755 113000.00000000
1756 113000.00000000
1760 110000.00000000
1763 24500.00000000
1764 24500.00000000
1765 52000.00000000
1789 5500.00000000
1791 5500.00000000
1792 10000.00000000
1793 5500.00000000
1801 37000.00000000
1802 35000.00000000
1810 285000.00000000
1816 56000.00000000
1840 110000.00000000
1844 110000.00000000
1852 22000.00000000
1867 0.00000000
1872 0.00000000
1891 97000.00000000
1959 95000.00000000