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
  ) 
  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.00239

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": 48,
      "filtered": 10.71428299,
      "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)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
65 68000.00000000
247 42000.00000000
255 66000.00000000
296 32000.00000000
706 105000.00000000
710 110000.00000000
820 98000.00000000
1011 106000.00000000
1145 29000.00000000
1188 14500.00000000
1190 21000.00000000
1202 46000.00000000
1203 12500.00000000
1207 12500.00000000
1210 12500.00000000
1218 335000.00000000
1389 12500.00000000
1472 145000.00000000
1651 58000.00000000
1661 66000.00000000
1685 200000.00000000
1689 106000.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
1753 110000.00000000
1754 113000.00000000
1755 113000.00000000
1756 113000.00000000
1760 110000.00000000
1765 52000.00000000
1789 5500.00000000
1791 5500.00000000
1792 10000.00000000
1793 5500.00000000
1816 56000.00000000
1840 110000.00000000
1844 110000.00000000
1867 0.00000000
1891 97000.00000000
1959 95000.00000000