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 (
    1343, 1338, 1337, 1174, 1486, 718, 1339, 
    1417, 341, 1324, 1275, 1216, 1458, 819, 
    1460, 1464, 1455, 1450, 1382, 1385, 
    1383, 1386, 25, 1758, 1757, 1734, 1176, 
    1201, 1191, 1182, 1825, 1824, 1821, 
    1761, 1823, 1822, 1459, 1477, 1193, 
    1546, 1254, 1684, 1379, 1378, 701, 699, 
    711, 703
  ) 
  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.00170

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": 9.795915604,
      "index_condition": "cscart_product_prices.product_id in (1343,1338,1337,1174,1486,718,1339,1417,341,1324,1275,1216,1458,819,1460,1464,1455,1450,1382,1385,1383,1386,25,1758,1757,1734,1176,1201,1191,1182,1825,1824,1821,1761,1823,1822,1459,1477,1193,1546,1254,1684,1379,1378,701,699,711,703)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
25 0.00000000
341 122000.00000000
699 103000.00000000
701 113000.00000000
703 113000.00000000
711 110000.00000000
718 0.00000000
819 182000.00000000
1174 91000.00000000
1176 83000.00000000
1182 65000.00000000
1191 83000.00000000
1193 150000.00000000
1201 65000.00000000
1216 88000.00000000
1254 26000.00000000
1275 0.00000000
1324 116000.00000000
1337 88000.00000000
1338 88000.00000000
1339 100000.00000000
1343 116000.00000000
1378 12000.00000000
1379 12000.00000000
1382 11000.00000000
1383 11000.00000000
1385 9000.00000000
1386 11000.00000000
1417 122000.00000000
1450 47000.00000000
1455 47000.00000000
1458 45000.00000000
1459 0.00000000
1460 46000.00000000
1464 0.00000000
1477 100000.00000000
1486 118000.00000000
1546 10500.00000000
1684 19167.00000000
1734 14500.00000000
1757 14500.00000000
1758 14500.00000000
1761 83000.00000000
1821 64000.00000000
1822 60000.00000000
1823 44000.00000000
1824 44000.00000000
1825 83000.00000000