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 (
    4603, 4606, 4623, 4629, 4625, 4605, 6273, 
    4572, 4577, 4578, 4630, 4631, 4556, 
    4607, 4633, 4634, 4576, 4575, 4627, 
    4608, 4622, 4619, 4632, 4626, 4628, 
    4620, 4580, 6265, 4621, 4609, 6285, 
    6322, 6284, 6283, 6326, 6324, 6325, 
    6295, 6294, 6287
  ) 
  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.00092

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 (4603,4606,4623,4629,4625,4605,6273,4572,4577,4578,4630,4631,4556,4607,4633,4634,4576,4575,4627,4608,4622,4619,4632,4626,4628,4620,4580,6265,4621,4609,6285,6322,6284,6283,6326,6324,6325,6295,6294,6287))",
        "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
4556 650000.00000000
4572 585000.00000000
4575 650000.00000000
4576 690000.00000000
4577 675000.00000000
4578 950000.00000000
4580 890000.00000000
4603 975000.00000000
4605 1250000.00000000
4606 1090000.00000000
4607 1075000.00000000
4608 1350000.00000000
4609 1500000.00000000
4619 1570000.00000000
4620 1750000.00000000
4621 1650000.00000000
4622 1800000.00000000
4623 1825000.00000000
4625 1935000.00000000
4626 2000000.00000000
4627 2075000.00000000
4628 2400000.00000000
4629 2800000.00000000
4630 3050000.00000000
4631 3275000.00000000
4632 3300000.00000000
4633 4250000.00000000
4634 6160000.00000000
6265 7105000.00000000
6273 5665000.00000000
6283 5220000.00000000
6284 5032000.00000000
6285 4670000.00000000
6287 3234000.00000000
6294 2024000.00000000
6295 1279000.00000000
6322 1144000.00000000
6324 812000.00000000
6325 602000.00000000
6326 629000.00000000