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 (
    1230, 1227, 77, 1235, 1958, 1712, 1759, 
    1002, 1381, 1320, 359, 1892, 1176, 1201, 
    1191, 1182, 1825, 1824, 1821, 1761, 
    1823, 1822, 1193, 701, 699, 711, 703, 
    710, 706, 1760, 1844, 1689, 1754, 1755, 
    1840, 1756, 1753, 820, 1472, 1145, 1202, 
    247, 296, 1765, 1816, 1766, 1219, 1220
  ) 
  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.00190

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": 50,
      "filtered": 11.73469067,
      "index_condition": "cscart_product_prices.product_id in (1230,1227,77,1235,1958,1712,1759,1002,1381,1320,359,1892,1176,1201,1191,1182,1825,1824,1821,1761,1823,1822,1193,701,699,711,703,710,706,1760,1844,1689,1754,1755,1840,1756,1753,820,1472,1145,1202,247,296,1765,1816,1766,1219,1220)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
77 58000.00000000
247 42000.00000000
296 32000.00000000
359 155000.00000000
699 103000.00000000
701 113000.00000000
703 113000.00000000
706 105000.00000000
710 110000.00000000
711 110000.00000000
820 98000.00000000
1002 35000.00000000
1145 29000.00000000
1176 83000.00000000
1182 65000.00000000
1191 83000.00000000
1193 150000.00000000
1201 65000.00000000
1202 46000.00000000
1219 56000.00000000
1220 56000.00000000
1227 125000.00000000
1230 45000.00000000
1235 108000.00000000
1320 22000.00000000
1381 30000.00000000
1472 145000.00000000
1689 106000.00000000
1712 27000.00000000
1753 110000.00000000
1754 113000.00000000
1755 113000.00000000
1756 113000.00000000
1759 27000.00000000
1760 110000.00000000
1761 83000.00000000
1765 52000.00000000
1766 115000.00000000
1816 56000.00000000
1821 64000.00000000
1822 60000.00000000
1823 44000.00000000
1824 44000.00000000
1825 83000.00000000
1840 110000.00000000
1844 110000.00000000
1892 145000.00000000
1958 0.00000000