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 (
    827, 840, 842, 889, 1085, 1100, 1106, 
    1150, 1201, 1212, 1216, 1226, 1242, 
    1253, 1309, 1434, 1436, 1462, 1514, 
    1570, 1618, 1851, 1969, 1979, 2036, 
    2046, 2047, 2063, 2165, 2185, 2315, 
    2345
  ) 
  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.00104

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": 32,
      "filtered": 4.285235882,
      "index_condition": "cscart_product_prices.product_id in (827,840,842,889,1085,1100,1106,1150,1201,1212,1216,1226,1242,1253,1309,1434,1436,1462,1514,1570,1618,1851,1969,1979,2036,2046,2047,2063,2165,2185,2315,2345)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
827 0.2100000000000000
840 0.2100000000000000
842 0.2100000000000000
889 0.2100000000000000
1085 0.6600000000000000
1100 0.6600000000000000
1106 0.6600000000000000
1150 0.4400000000000000
1201 0.8800000000000000
1212 0.8800000000000000
1216 0.8800000000000000
1226 15.0000000000000000
1242 12.8400000000000000
1253 20.0000000000000000
1309 0.6100000000000000
1434 0.5900000000000000
1436 0.5900000000000000
1462 0.9700000000000000
1514 0.9900000000000000
1570 0.9900000000000000
1618 0.9900000000000000
1851 0.9900000000000000
1969 0.9900000000000000
1979 0.9900000000000000
2036 37.4500000000000000
2046 37.4500000000000000
2047 37.4500000000000000
2063 37.4500000000000000
2165 37.4500000000000000
2185 37.4500000000000000
2315 37.4500000000000000
2345 37.4500000000000000