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 (
    4678, 4817, 4839, 4674, 4519, 4823, 4693, 
    4673, 4816, 4516, 4695, 4822, 4334, 
    4681, 4675, 4688, 4696, 4827, 4820, 
    4830, 4679, 4814, 4819, 4322, 4834, 
    4694, 4682, 4690, 4261, 4686, 4672, 
    4510, 4815, 4689, 4687, 4826, 4677, 
    4829, 4050, 4691, 4697, 4692, 4523, 
    4676, 4658
  ) 
  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.00175

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "31.51"
    },
    "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": 45,
        "rows_produced_per_join": 8,
        "filtered": "19.99",
        "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`product_id` in (4678,4817,4839,4674,4519,4823,4693,4673,4816,4516,4695,4822,4334,4681,4675,4688,4696,4827,4820,4830,4679,4814,4819,4322,4834,4694,4682,4690,4261,4686,4672,4510,4815,4689,4687,4826,4677,4829,4050,4691,4697,4692,4523,4676,4658))",
        "cost_info": {
          "read_cost": "30.61",
          "eval_cost": "0.90",
          "prefix_cost": "31.51",
          "data_read_per_join": "215"
        },
        "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
4050 8000.00000000
4261 7000.00000000
4322 20000.00000000
4334 6000.00000000
4510 7000.00000000
4516 6000.00000000
4519 10000.00000000
4523 15000.00000000
4658 5000.00000000
4672 25000.00000000
4673 45000.00000000
4674 60000.00000000
4675 60000.00000000
4676 7000.00000000
4677 7000.00000000
4678 25000.00000000
4679 30000.00000000
4681 10000.00000000
4682 12000.00000000
4686 8000.00000000
4687 30000.00000000
4688 30000.00000000
4689 30000.00000000
4690 30000.00000000
4691 30000.00000000
4692 30000.00000000
4693 50000.00000000
4694 45000.00000000
4695 35000.00000000
4696 25000.00000000
4697 10000.00000000
4814 40000.00000000
4815 40000.00000000
4816 40000.00000000
4817 40000.00000000
4819 40000.00000000
4820 40000.00000000
4822 50000.00000000
4823 49000.00000000
4826 35000.00000000
4827 30000.00000000
4829 40000.00000000
4830 35000.00000000
4834 40000.00000000
4839 14000.00000000