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 (
    1947, 1458, 1460, 1464, 1455, 1382, 1385, 
    1383, 1386, 25, 1758, 1757, 1734, 1254, 
    1684, 1379, 1378, 1792, 1791, 1793, 
    1789, 1716, 1714, 1715, 1720, 1719, 
    1722, 1713, 1718, 1717, 1867, 1721, 
    1685, 1207, 1210, 1203, 1389, 1188, 
    1190, 1209, 1186, 1852, 1456, 1457, 
    1045, 1046, 1187, 1577, 1194, 368, 1253
  ) 
  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.00198

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": 53,
      "filtered": 10.40816021,
      "index_condition": "cscart_product_prices.product_id in (1947,1458,1460,1464,1455,1382,1385,1383,1386,25,1758,1757,1734,1254,1684,1379,1378,1792,1791,1793,1789,1716,1714,1715,1720,1719,1722,1713,1718,1717,1867,1721,1685,1207,1210,1203,1389,1188,1190,1209,1186,1852,1456,1457,1045,1046,1187,1577,1194,368,1253)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
25 0.00000000
368 0.00000000
1045 72000.00000000
1046 78000.00000000
1186 21000.00000000
1187 47000.00000000
1188 14500.00000000
1190 21000.00000000
1194 42000.00000000
1203 12500.00000000
1207 12500.00000000
1209 21000.00000000
1210 12500.00000000
1253 40000.00000000
1254 26000.00000000
1378 12000.00000000
1379 12000.00000000
1382 11000.00000000
1383 11000.00000000
1385 9000.00000000
1386 11000.00000000
1389 12500.00000000
1455 47000.00000000
1456 22000.00000000
1457 0.00000000
1458 45000.00000000
1460 46000.00000000
1464 0.00000000
1577 30000.00000000
1684 19167.00000000
1685 200000.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
1734 14500.00000000
1757 14500.00000000
1758 14500.00000000
1789 5500.00000000
1791 5500.00000000
1792 10000.00000000
1793 5500.00000000
1852 22000.00000000
1867 0.00000000
1947 100000.00000000