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 (
    1, 228, 8, 9, 5290, 10, 8483, 2892, 71, 
    2896, 2902, 4, 2359, 1532, 2906, 5271, 
    6340, 8499, 8502, 5851, 8501, 4974, 
    5277, 2459, 5640, 5284, 5376, 5287, 
    5281, 5397, 4995, 5270, 2901, 5278, 
    5187, 5549, 5235, 1528, 2776, 2432, 
    2771, 4992, 5661, 2774, 5207, 2455, 
    5737, 6385, 5186, 1517, 5008, 5272, 
    4991, 2438, 5276, 8498, 5660, 7624, 
    8484, 8497, 5285, 5346, 2812, 5728, 
    7608, 5879, 2250, 8485, 1538, 5265, 
    8488, 8486, 8492, 2899, 5288, 8487, 
    5523, 7614, 5666, 6823
  ) 
  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.00166

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "56.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": 80,
        "rows_produced_per_join": 15,
        "filtered": "19.99",
        "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`product_id` in (1,228,8,9,5290,10,8483,2892,71,2896,2902,4,2359,1532,2906,5271,6340,8499,8502,5851,8501,4974,5277,2459,5640,5284,5376,5287,5281,5397,4995,5270,2901,5278,5187,5549,5235,1528,2776,2432,2771,4992,5661,2774,5207,2455,5737,6385,5186,1517,5008,5272,4991,2438,5276,8498,5660,7624,8484,8497,5285,5346,2812,5728,7608,5879,2250,8485,1538,5265,8488,8486,8492,2899,5288,8487,5523,7614,5666,6823))",
        "cost_info": {
          "read_cost": "54.41",
          "eval_cost": "1.60",
          "prefix_cost": "56.01",
          "data_read_per_join": "383"
        },
        "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
1 1450000.00000000
4 1050000.00000000
8 1400000.00000000
9 865000.00000000
10 1800000.00000000
71 1550000.00000000
228 2200000.00000000
1517 1812000.00000000
1528 1630000.00000000
1532 845000.00000000
1538 935000.00000000
2250 61000.00000000
2359 298000.00000000
2432 1005000.00000000
2438 445000.00000000
2455 975000.00000000
2459 671000.00000000
2771 40000.00000000
2774 73000.00000000
2776 314000.00000000
2812 900000.00000000
2892 380000.00000000
2896 648000.00000000
2899 115000.00000000
2901 672000.00000000
2902 209000.00000000
2906 82000.00000000
4974 269250.00000000
4991 299250.00000000
4992 182000.00000000
4995 156750.00000000
5008 13950.00000000
5186 35000.00000000
5187 35000.00000000
5207 8250.00000000
5235 35000.00000000
5265 8000.00000000
5270 8000.00000000
5271 8000.00000000
5272 8000.00000000
5276 8000.00000000
5277 8000.00000000
5278 8000.00000000
5281 8000.00000000
5284 8000.00000000
5285 8000.00000000
5287 8000.00000000
5288 8000.00000000
5290 8000.00000000
5346 735000.00000000
5376 825000.00000000
5397 1050000.00000000
5523 94500.00000000
5549 422500.00000000
5640 20000.00000000
5660 45250.00000000
5661 58500.00000000
5666 168000.00000000
5728 1282500.00000000
5737 823500.00000000
5851 985500.00000000
5879 958500.00000000
6340 699000.00000000
6385 690000.00000000
6823 67000.00000000
7608 1610000.00000000
7614 325000.00000000
7624 895000.00000000
8483 370000.00000000
8484 560000.00000000
8485 735000.00000000
8486 361000.00000000
8487 552000.00000000
8488 625000.00000000
8492 370000.00000000
8497 3375000.00000000
8498 2132000.00000000
8499 3175000.00000000
8501 550000.00000000
8502 2129000.00000000