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 (
    9314, 9341, 9368, 9374, 9375, 9397, 9408, 
    9481, 9504, 9505, 9525, 8997, 9126, 
    9149, 9150, 9154, 9155, 9158, 9178, 
    9186, 9191, 9240, 9242, 9284, 9285, 
    9315, 9316, 9317, 9319, 9336, 9338, 
    9343, 9347, 9352, 9359, 9366, 9379, 
    9385, 9387, 9392, 9398, 9401, 9402, 
    9407, 9410, 9414, 9415, 9419, 9420, 
    9426, 9427, 9478, 9479, 9480, 9482, 
    9498, 9501, 9513, 9518, 9524, 8996, 
    8998, 9021, 9031, 9112, 9156, 9180, 
    9218, 9243, 9283, 9289, 9294, 9335, 
    9339, 9340, 9344, 9348, 9351, 9353, 
    9355
  ) 
  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.00204

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 (9314,9341,9368,9374,9375,9397,9408,9481,9504,9505,9525,8997,9126,9149,9150,9154,9155,9158,9178,9186,9191,9240,9242,9284,9285,9315,9316,9317,9319,9336,9338,9343,9347,9352,9359,9366,9379,9385,9387,9392,9398,9401,9402,9407,9410,9414,9415,9419,9420,9426,9427,9478,9479,9480,9482,9498,9501,9513,9518,9524,8996,8998,9021,9031,9112,9156,9180,9218,9243,9283,9289,9294,9335,9339,9340,9344,9348,9351,9353,9355))",
        "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
8996 20000.00000000
8997 20000.00000000
8998 25500.00000000
9021 25500.00000000
9031 51000.00000000
9112 15000.00000000
9126 15000.00000000
9149 17000.00000000
9150 15000.00000000
9154 20000.00000000
9155 20000.00000000
9156 20000.00000000
9158 17000.00000000
9178 73250.00000000
9180 15000.00000000
9186 15000.00000000
9191 24000.00000000
9218 30750.00000000
9240 15000.00000000
9242 20000.00000000
9243 20000.00000000
9283 20000.00000000
9284 20000.00000000
9285 20000.00000000
9289 18000.00000000
9294 36750.00000000
9314 9750.00000000
9315 15000.00000000
9316 15000.00000000
9317 15000.00000000
9319 15000.00000000
9335 15000.00000000
9336 15000.00000000
9338 15000.00000000
9339 15000.00000000
9340 15000.00000000
9341 15000.00000000
9343 15000.00000000
9344 15000.00000000
9347 15000.00000000
9348 15000.00000000
9351 15000.00000000
9352 15000.00000000
9353 15000.00000000
9355 15000.00000000
9359 15000.00000000
9366 24000.00000000
9368 8000.00000000
9374 10000.00000000
9375 11000.00000000
9379 42750.00000000
9385 5500.00000000
9387 112000.00000000
9392 34000.00000000
9397 4500.00000000
9398 22500.00000000
9401 20250.00000000
9402 5000.00000000
9407 14250.00000000
9408 10250.00000000
9410 10250.00000000
9414 10000.00000000
9415 8000.00000000
9419 11000.00000000
9420 13000.00000000
9426 10000.00000000
9427 10000.00000000
9478 6500.00000000
9479 10500.00000000
9480 10500.00000000
9481 10500.00000000
9482 10500.00000000
9498 5500.00000000
9501 30750.00000000
9504 25500.00000000
9505 6000.00000000
9513 24500.00000000
9518 38500.00000000
9524 7000.00000000
9525 9000.00000000