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 (
    3354, 3308, 3397, 3841, 3212, 3928, 3660, 
    3828, 3518, 3918, 3400, 3232, 3740, 
    3743, 3741, 3440, 3441, 10078, 3768, 
    3747, 3406, 3827, 3738, 3403, 3443, 
    3737, 3655, 3658, 3769, 3745, 3767, 
    3950, 3659, 3766, 3739, 3444, 3785, 
    3442, 3787, 3770, 3447, 3793, 3450, 
    3772, 3795, 3788, 3439, 3446, 3794, 
    3948, 3449, 3871, 3786, 3867, 3951, 
    3949, 3881, 11629, 8597, 8280, 8443, 
    8596, 11632, 8405, 8584, 8588, 8437, 
    8515, 8542, 10446, 10492, 11623, 11624, 
    11625, 11626, 11627, 11628, 11631
  ) 
  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.00187

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "86.56"
    },
    "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": 149,
        "rows_produced_per_join": 29,
        "filtered": "19.99",
        "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`product_id` in (3354,3308,3397,3841,3212,3928,3660,3828,3518,3918,3400,3232,3740,3743,3741,3440,3441,10078,3768,3747,3406,3827,3738,3403,3443,3737,3655,3658,3769,3745,3767,3950,3659,3766,3739,3444,3785,3442,3787,3770,3447,3793,3450,3772,3795,3788,3439,3446,3794,3948,3449,3871,3786,3867,3951,3949,3881,11629,8597,8280,8443,8596,11632,8405,8584,8588,8437,8515,8542,10446,10492,11623,11624,11625,11626,11627,11628,11631))",
        "cost_info": {
          "read_cost": "83.58",
          "eval_cost": "2.98",
          "prefix_cost": "86.56",
          "data_read_per_join": "714"
        },
        "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
3212 4000.00000000
3232 15000.00000000
3308 21000.00000000
3354 28000.00000000
3397 15000.00000000
3400 15000.00000000
3403 20000.00000000
3406 20000.00000000
3439 9000.00000000
3440 9000.00000000
3441 9000.00000000
3442 9000.00000000
3443 9000.00000000
3444 9000.00000000
3446 9000.00000000
3447 9000.00000000
3449 7500.00000000
3450 7500.00000000
3518 7000.00000000
3655 7500.00000000
3658 7500.00000000
3659 7500.00000000
3660 7500.00000000
3737 6000.00000000
3738 6000.00000000
3739 6000.00000000
3740 6000.00000000
3741 6000.00000000
3743 6000.00000000
3745 4500.00000000
3747 4500.00000000
3766 4500.00000000
3767 4500.00000000
3768 4500.00000000
3769 4500.00000000
3770 4500.00000000
3772 4500.00000000
3785 4500.00000000
3786 4500.00000000
3787 4500.00000000
3788 4500.00000000
3793 4500.00000000
3794 4500.00000000
3795 4500.00000000
3827 22000.00000000
3828 23000.00000000
3841 28000.00000000
3867 10000.00000000
3871 12000.00000000
3881 12000.00000000
3918 89000.00000000
3928 18000.00000000
3948 25000.00000000
3949 25000.00000000
3950 25000.00000000
3951 25000.00000000
8280 39000.00000000
8405 21000.00000000
8437 19000.00000000
8443 25000.00000000
8515 29000.00000000
8542 19000.00000000
8584 22000.00000000
8588 19000.00000000
8596 15000.00000000
8597 15000.00000000
10078 7000.00000000
10446 15000.00000000
10492 7000.00000000
11623 10000.00000000
11624 10000.00000000
11625 10000.00000000
11626 10000.00000000
11627 15000.00000000
11628 15000.00000000
11629 15000.00000000
11631 30000.00000000
11632 30000.00000000