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 (
    5290, 5271, 4974, 5277, 5640, 5284, 5376, 
    5287, 5281, 5397, 4995, 5270, 5278, 
    5187, 5549, 5235, 4992, 5207, 5186, 
    5008, 5272, 4991, 5276, 5285, 5346, 
    5265, 5288, 6823, 5179, 5263, 5112, 
    5671, 5259, 5215, 5289, 5472, 5387, 
    5969, 5303, 5205
  ) 
  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.00202

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 (5290,5271,4974,5277,5640,5284,5376,5287,5281,5397,4995,5270,5278,5187,5549,5235,4992,5207,5186,5008,5272,4991,5276,5285,5346,5265,5288,6823,5179,5263,5112,5671,5259,5215,5289,5472,5387,5969,5303,5205))",
        "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
4974 269250.00000000
4991 299250.00000000
4992 182000.00000000
4995 156750.00000000
5008 13950.00000000
5112 35000.00000000
5179 8250.00000000
5186 35000.00000000
5187 35000.00000000
5205 8250.00000000
5207 8250.00000000
5215 18000.00000000
5235 35000.00000000
5259 39000.00000000
5263 8000.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
5289 8000.00000000
5290 8000.00000000
5303 17000.00000000
5346 735000.00000000
5376 825000.00000000
5387 90000.00000000
5397 1050000.00000000
5472 6000000.00000000
5549 422500.00000000
5640 20000.00000000
5671 20000.00000000
5969 13950.00000000
6823 67000.00000000