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 (
    5032, 5028, 5030, 5029, 5036, 5461, 5458, 
    5328, 5047, 5365, 5033, 5456, 5596, 
    5367, 5500, 5054, 5335, 5504, 5506, 
    5464, 5508, 6081, 6144, 6083, 6189, 
    6145, 5644, 6297, 6190, 6276, 6303, 
    6260, 6305, 6274, 6301, 6824, 6825
  ) 
  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.00138

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "25.91"
    },
    "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": 37,
        "rows_produced_per_join": 7,
        "filtered": "19.99",
        "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`product_id` in (5032,5028,5030,5029,5036,5461,5458,5328,5047,5365,5033,5456,5596,5367,5500,5054,5335,5504,5506,5464,5508,6081,6144,6083,6189,6145,5644,6297,6190,6276,6303,6260,6305,6274,6301,6824,6825))",
        "cost_info": {
          "read_cost": "25.17",
          "eval_cost": "0.74",
          "prefix_cost": "25.91",
          "data_read_per_join": "177"
        },
        "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
5028 57000.00000000
5029 750000.00000000
5030 1326000.00000000
5032 927000.00000000
5033 255000.00000000
5036 225000.00000000
5047 63000.00000000
5054 48000.00000000
5328 18000.00000000
5335 15000.00000000
5365 31000.00000000
5367 23000.00000000
5456 31000.00000000
5458 39000.00000000
5461 33000.00000000
5464 101500.00000000
5500 41000.00000000
5504 80000.00000000
5506 92000.00000000
5508 170000.00000000
5596 106000.00000000
5644 103500.00000000
6081 128000.00000000
6083 118000.00000000
6144 192000.00000000
6145 251000.00000000
6189 117500.00000000
6190 124500.00000000
6260 57000.00000000
6274 65000.00000000
6276 54000.00000000
6297 85000.00000000
6301 97000.00000000
6303 66000.00000000
6305 53500.00000000
6824 82000.00000000
6825 76000.00000000