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 (
    10479, 10484, 10539, 10541, 10544, 10987, 
    11005, 11050, 11057, 11065, 11076, 
    11091, 11180, 11184, 11248, 11253, 
    11312, 7738, 7748, 7756, 8858, 8871, 
    8946, 10070, 10173, 10245, 10260, 10418, 
    10439, 10441, 10443, 10444, 10464, 
    10489, 10546, 10550, 10567, 10568, 
    10570, 10986
  ) 
  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.00116

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "56.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": "usergroup",
        "used_key_parts": [
          "product_id",
          "usergroup_id",
          "lower_limit"
        ],
        "key_length": "9",
        "rows_examined_per_scan": 80,
        "rows_produced_per_join": 80,
        "filtered": "100.00",
        "index_condition": "((`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`lower_limit` = 1) and (`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`product_id` in (10479,10484,10539,10541,10544,10987,11005,11050,11057,11065,11076,11091,11180,11184,11248,11253,11312,7738,7748,7756,8858,8871,8946,10070,10173,10245,10260,10418,10439,10441,10443,10444,10464,10489,10546,10550,10567,10568,10570,10986)) and (`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`usergroup_id` in (0,1)))",
        "cost_info": {
          "read_cost": "48.01",
          "eval_cost": "8.00",
          "prefix_cost": "56.01",
          "data_read_per_join": "1K"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ]
      }
    }
  }
}

Result

product_id price
7738 218000.00000000
7748 192000.00000000
7756 200000.00000000
8858 329000.00000000
8871 529000.00000000
8946 2030000.00000000
10070 95000.00000000
10173 22000.00000000
10245 27000.00000000
10260 16000.00000000
10418 59000.00000000
10439 85000.00000000
10441 97000.00000000
10443 34750.00000000
10444 38750.00000000
10464 65000.00000000
10479 179000.00000000
10484 44750.00000000
10489 184750.00000000
10539 170000.00000000
10541 424750.00000000
10544 509750.00000000
10546 379750.00000000
10550 234750.00000000
10567 60000.00000000
10568 38000.00000000
10570 75000.00000000
10986 55000.00000000
10987 95000.00000000
11005 145000.00000000
11050 95000.00000000
11057 35000.00000000
11065 165000.00000000
11076 45000.00000000
11091 55000.00000000
11180 1000000.00000000
11184 50000.00000000
11248 245000.00000000
11253 210000.00000000
11312 1100000.00000000