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 (
    10242, 8381, 10072, 10231, 10246, 10251, 
    10262, 10269, 10271, 10280, 10158, 
    10021, 10030, 10044, 10045, 10076, 
    10099, 10106, 10123, 10164, 10166, 
    10235, 10253, 10256, 10257, 10268, 
    10270, 10279, 10281, 10297, 9714, 10046, 
    10107, 10155, 10163, 10168, 10203, 
    10240, 10252, 10263, 10266, 10298, 
    10094, 10096, 10108, 10153, 10162, 
    10165, 10169, 10170, 10220, 10259, 
    10031, 10070, 10173, 10245, 10258, 
    10260, 10041, 10241, 10184, 10296, 
    10067, 10149, 10064
  ) 
  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.00117

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "81.06"
    },
    "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": 144,
        "rows_produced_per_join": 28,
        "filtered": "19.99",
        "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`product_id` in (10242,8381,10072,10231,10246,10251,10262,10269,10271,10280,10158,10021,10030,10044,10045,10076,10099,10106,10123,10164,10166,10235,10253,10256,10257,10268,10270,10279,10281,10297,9714,10046,10107,10155,10163,10168,10203,10240,10252,10263,10266,10298,10094,10096,10108,10153,10162,10165,10169,10170,10220,10259,10031,10070,10173,10245,10258,10260,10041,10241,10184,10296,10067,10149,10064))",
        "cost_info": {
          "read_cost": "78.18",
          "eval_cost": "2.88",
          "prefix_cost": "81.06",
          "data_read_per_join": "690"
        },
        "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
8381 43000.00000000
9714 520000.00000000
10021 119000.00000000
10030 18000.00000000
10031 18000.00000000
10041 75000.00000000
10044 19000.00000000
10045 60000.00000000
10046 25000.00000000
10064 185000.00000000
10067 75000.00000000
10070 95000.00000000
10072 35000.00000000
10076 52000.00000000
10094 477000.00000000
10096 33000.00000000
10099 18000.00000000
10106 22000.00000000
10107 12000.00000000
10108 96000.00000000
10123 50000.00000000
10149 33000.00000000
10153 30000.00000000
10155 18000.00000000
10158 55000.00000000
10162 57000.00000000
10163 75000.00000000
10164 107000.00000000
10165 18000.00000000
10166 15000.00000000
10168 30000.00000000
10169 57000.00000000
10170 67000.00000000
10173 22000.00000000
10184 82000.00000000
10203 28000.00000000
10220 15000.00000000
10231 10000.00000000
10235 13000.00000000
10240 12000.00000000
10241 133000.00000000
10242 10000.00000000
10245 27000.00000000
10246 9000.00000000
10251 6000.00000000
10252 13000.00000000
10253 21000.00000000
10256 10000.00000000
10257 13000.00000000
10258 25000.00000000
10259 98000.00000000
10260 16000.00000000
10262 43000.00000000
10263 45000.00000000
10266 49000.00000000
10268 10000.00000000
10269 16000.00000000
10270 13000.00000000
10271 13000.00000000
10279 21000.00000000
10280 9000.00000000
10281 10000.00000000
10296 9000.00000000
10297 12000.00000000
10298 33000.00000000