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 (
    2305, 48558, 40449, 34674, 34668, 3862, 
    2857, 48235, 6368, 3519, 15322, 6274, 
    48559, 30368, 40381, 47828, 46583, 
    48557, 1945, 44946, 22263, 34667, 1267, 
    3863, 2859, 1105, 35730, 1107, 35177, 
    48069, 854, 44943
  ) 
  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.01052

JSON explain

{
  "query_block": {
    "select_id": 1,
    "nested_loop": [
      {
        "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": 99.9960556,
          "index_condition": "cscart_product_prices.product_id in (2305,48558,40449,34674,34668,3862,2857,48235,6368,3519,15322,6274,48559,30368,40381,47828,46583,48557,1945,44946,22263,34667,1267,3863,2859,1105,35730,1107,35177,48069,854,44943)",
          "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
        }
      }
    ]
  }
}

Result

product_id price
854 1680000.0000
1105 583000.0000
1107 583000.0000
1267 160000.0000
1945 450000.0000
2305 350000.0000
2857 450000.0000
2859 550000.0000
3519 169000.0000
3862 550000.0000
3863 550000.0000
6274 110000.0000
6368 790000.0000
15322 225000.0000
22263 150000.0000
30368 220000.0000
34667 455000.0000
34668 115000.0000
34674 65000.0000
35177 665000.0000
35730 160000.0000
40381 240000.0000
40449 230000.0000
44943 680000.0000
44946 700000.0000
46583 230000.0000
47828 295000.0000
48069 95000.0000
48235 290000.0000
48557 495000.0000
48558 616000.0000
48559 616000.0000