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 (
    2574, 247, 2478, 908, 2527, 2490, 2372, 
    8483, 2509, 2892, 2896, 2471, 2576, 
    2902, 2395, 2620, 2359, 2340, 2502, 
    2473, 2508, 2565, 2906, 2580, 2638, 
    2550, 2275, 2363, 2599, 2399, 2352, 
    2503, 2602, 2459, 2339, 2360, 2572, 
    2253, 2540, 2107
  ) 
  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.00143

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "28.01"
    },
    "grouping_operation": {
      "using_filesort": false,
      "table": {
        "table_name": "cscart_product_prices",
        "access_type": "range",
        "possible_keys": [
          "usergroup",
          "product_id",
          "lower_limit",
          "usergroup_id"
        ],
        "key": "product_id",
        "used_key_parts": [
          "product_id"
        ],
        "key_length": "3",
        "rows_examined_per_scan": 40,
        "rows_produced_per_join": 7,
        "filtered": "19.99",
        "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`product_id` in (2574,247,2478,908,2527,2490,2372,8483,2509,2892,2896,2471,2576,2902,2395,2620,2359,2340,2502,2473,2508,2565,2906,2580,2638,2550,2275,2363,2599,2399,2352,2503,2602,2459,2339,2360,2572,2253,2540,2107))",
        "cost_info": {
          "read_cost": "27.21",
          "eval_cost": "0.80",
          "prefix_cost": "28.01",
          "data_read_per_join": "191"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ],
        "attached_condition": "((`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`lower_limit` = 1) and (`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}

Result

product_id price
247 97000.00000000
908 209000.00000000
2107 209000.00000000
2253 15000.00000000
2275 21000.00000000
2339 24000.00000000
2340 7000.00000000
2352 12000.00000000
2359 298000.00000000
2360 67000.00000000
2363 51000.00000000
2372 21000.00000000
2395 22000.00000000
2399 27000.00000000
2459 671000.00000000
2471 1455000.00000000
2473 1032000.00000000
2478 850000.00000000
2490 599000.00000000
2502 1446000.00000000
2503 1027000.00000000
2508 699000.00000000
2509 420000.00000000
2527 93000.00000000
2540 527000.00000000
2550 440000.00000000
2565 282000.00000000
2572 252000.00000000
2574 337000.00000000
2576 276000.00000000
2580 975000.00000000
2599 483000.00000000
2602 379000.00000000
2620 116000.00000000
2638 330000.00000000
2892 380000.00000000
2896 648000.00000000
2902 209000.00000000
2906 82000.00000000
8483 370000.00000000