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 (
    8663, 8779, 8562, 8657, 8689, 8627, 8619, 
    8766, 8838, 8936, 10020, 8910, 8731, 
    10167, 8573, 8916, 8601, 8568, 8655, 
    8667, 8618, 8536, 8566, 8776, 8590, 
    8560, 8914, 8933, 8739, 8748, 8629, 
    8775, 8787, 8783, 8737, 8470, 8742, 
    8567, 8815, 8683, 10174, 8575, 9972, 
    10104, 8581, 9952, 9937, 9968, 8538, 
    8572, 8578, 8580, 9969, 10093, 8540, 
    8574, 8605, 8582, 8599, 10021, 8541
  ) 
  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.00149

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "42.71"
    },
    "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": 61,
        "rows_produced_per_join": 12,
        "filtered": "19.99",
        "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`product_id` in (8663,8779,8562,8657,8689,8627,8619,8766,8838,8936,10020,8910,8731,10167,8573,8916,8601,8568,8655,8667,8618,8536,8566,8776,8590,8560,8914,8933,8739,8748,8629,8775,8787,8783,8737,8470,8742,8567,8815,8683,10174,8575,9972,10104,8581,9952,9937,9968,8538,8572,8578,8580,9969,10093,8540,8574,8605,8582,8599,10021,8541))",
        "cost_info": {
          "read_cost": "41.49",
          "eval_cost": "1.22",
          "prefix_cost": "42.71",
          "data_read_per_join": "292"
        },
        "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
8470 38000.00000000
8536 83000.00000000
8538 79000.00000000
8540 79000.00000000
8541 89000.00000000
8560 84000.00000000
8562 75000.00000000
8566 181000.00000000
8567 89000.00000000
8568 45000.00000000
8572 55000.00000000
8573 59000.00000000
8574 66000.00000000
8575 79000.00000000
8578 49000.00000000
8580 55000.00000000
8581 59000.00000000
8582 59000.00000000
8590 44000.00000000
8599 109000.00000000
8601 98000.00000000
8605 38000.00000000
8618 32000.00000000
8619 39000.00000000
8627 38000.00000000
8629 46000.00000000
8655 23000.00000000
8657 26000.00000000
8663 21000.00000000
8667 46000.00000000
8683 45000.00000000
8689 49000.00000000
8731 28000.00000000
8737 76000.00000000
8739 67000.00000000
8742 60000.00000000
8748 55000.00000000
8766 59000.00000000
8775 199000.00000000
8776 62000.00000000
8779 46000.00000000
8783 49000.00000000
8787 59000.00000000
8815 29000.00000000
8838 36000.00000000
8910 36000.00000000
8914 49000.00000000
8916 48000.00000000
8933 53000.00000000
8936 29000.00000000
9937 63000.00000000
9952 21000.00000000
9968 28000.00000000
9969 75000.00000000
9972 112000.00000000
10020 150000.00000000
10021 119000.00000000
10093 21000.00000000
10104 203000.00000000
10167 10000.00000000
10174 82000.00000000