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 (
    4390, 4517, 4690, 4326, 4261, 4686, 4383, 
    4705, 4037, 4664, 4703, 4141, 4518, 
    4052, 4077, 4031, 4525, 4114, 4698, 
    4535, 4672, 4386, 3989, 4096, 4382, 
    4074, 4670, 4145, 4453, 4385, 4669, 
    4389, 4655, 4069, 4023, 4510, 4098, 
    4158, 4339, 4515, 4064, 4680, 4040, 
    4671, 3991, 4047, 4388, 4100, 3990, 
    4815, 4684, 4699, 4075, 4048, 4051, 
    4057, 4455, 4113, 4323, 4452, 4036, 
    4260, 4542, 4667, 4097, 4689, 4063, 
    4099, 4262, 4534, 4687, 4033, 4511, 
    4707, 4146, 4034, 4437, 4236, 4826, 
    4359
  ) 
  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.00222

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": "product_id",
        "used_key_parts": [
          "product_id"
        ],
        "key_length": "3",
        "rows_examined_per_scan": 80,
        "rows_produced_per_join": 15,
        "filtered": "19.99",
        "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`product_id` in (4390,4517,4690,4326,4261,4686,4383,4705,4037,4664,4703,4141,4518,4052,4077,4031,4525,4114,4698,4535,4672,4386,3989,4096,4382,4074,4670,4145,4453,4385,4669,4389,4655,4069,4023,4510,4098,4158,4339,4515,4064,4680,4040,4671,3991,4047,4388,4100,3990,4815,4684,4699,4075,4048,4051,4057,4455,4113,4323,4452,4036,4260,4542,4667,4097,4689,4063,4099,4262,4534,4687,4033,4511,4707,4146,4034,4437,4236,4826,4359))",
        "cost_info": {
          "read_cost": "54.41",
          "eval_cost": "1.60",
          "prefix_cost": "56.01",
          "data_read_per_join": "383"
        },
        "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
3989 6000.00000000
3990 5000.00000000
3991 4000.00000000
4023 40000.00000000
4031 4000.00000000
4033 4000.00000000
4034 5000.00000000
4036 4000.00000000
4037 4000.00000000
4040 6000.00000000
4047 5000.00000000
4048 3000.00000000
4051 7000.00000000
4052 7000.00000000
4057 6000.00000000
4063 30000.00000000
4064 6000.00000000
4069 10000.00000000
4074 7000.00000000
4075 6000.00000000
4077 6000.00000000
4096 5000.00000000
4097 5000.00000000
4098 5000.00000000
4099 5000.00000000
4100 5000.00000000
4113 7000.00000000
4114 6000.00000000
4141 15000.00000000
4145 10000.00000000
4146 4000.00000000
4158 5000.00000000
4236 6000.00000000
4260 6000.00000000
4261 7000.00000000
4262 7000.00000000
4323 20000.00000000
4326 15000.00000000
4339 6000.00000000
4359 30000.00000000
4382 5000.00000000
4383 5000.00000000
4385 5000.00000000
4386 5000.00000000
4388 5000.00000000
4389 5000.00000000
4390 5000.00000000
4437 30000.00000000
4452 30000.00000000
4453 30000.00000000
4455 30000.00000000
4510 7000.00000000
4511 5000.00000000
4515 8000.00000000
4517 6000.00000000
4518 7000.00000000
4525 6000.00000000
4534 6000.00000000
4535 6000.00000000
4542 6000.00000000
4655 200000.00000000
4664 100000.00000000
4667 7000.00000000
4669 40000.00000000
4670 40000.00000000
4671 25000.00000000
4672 25000.00000000
4680 10000.00000000
4684 5000.00000000
4686 8000.00000000
4687 30000.00000000
4689 30000.00000000
4690 30000.00000000
4698 6000.00000000
4699 6000.00000000
4703 6000.00000000
4705 6000.00000000
4707 5000.00000000
4815 40000.00000000
4826 35000.00000000