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 (
    3564, 3482, 3581, 3574, 3583, 3579, 3476, 
    3569, 3870, 3562, 3543, 3856, 3585, 
    3503, 3488, 2119, 3577, 3899, 3544, 
    3502, 3823, 3884, 3575, 3539, 3541, 
    2120, 3576, 3469, 3584, 3487, 3808, 
    3868, 3472, 3514, 3580, 3473, 3479, 
    3572, 3866, 3570, 3582, 3489, 3567, 
    3861, 3561, 3504, 3475, 3565, 3573, 
    3568, 3563, 3547, 3513, 3586, 3542, 
    3571, 3566, 3474, 3538, 3540, 3587
  ) 
  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.00214

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 (3564,3482,3581,3574,3583,3579,3476,3569,3870,3562,3543,3856,3585,3503,3488,2119,3577,3899,3544,3502,3823,3884,3575,3539,3541,2120,3576,3469,3584,3487,3808,3868,3472,3514,3580,3473,3479,3572,3866,3570,3582,3489,3567,3861,3561,3504,3475,3565,3573,3568,3563,3547,3513,3586,3542,3571,3566,3474,3538,3540,3587))",
        "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
2119 1045000.00000000
2120 913000.00000000
3469 2799000.00000000
3472 1225000.00000000
3473 2450000.00000000
3474 1250000.00000000
3475 1775000.00000000
3476 1675000.00000000
3479 4950000.00000000
3482 2475000.00000000
3487 1199000.00000000
3488 3899000.00000000
3489 5999000.00000000
3502 425000.00000000
3503 999000.00000000
3504 835000.00000000
3513 999000.00000000
3514 825000.00000000
3538 1025000.00000000
3539 575000.00000000
3540 1720000.00000000
3541 560000.00000000
3542 575000.00000000
3543 4899000.00000000
3544 3850000.00000000
3547 1290000.00000000
3561 3425000.00000000
3562 1999000.00000000
3563 1099000.00000000
3564 450000.00000000
3565 1625000.00000000
3566 735000.00000000
3567 1025000.00000000
3568 2640000.00000000
3569 1580000.00000000
3570 890000.00000000
3571 1050000.00000000
3572 1625000.00000000
3573 2450000.00000000
3574 2450000.00000000
3575 1385000.00000000
3576 1390000.00000000
3577 775000.00000000
3579 1175000.00000000
3580 1250000.00000000
3581 1310000.00000000
3582 2099000.00000000
3583 715000.00000000
3584 1725000.00000000
3585 1265000.00000000
3586 275000.00000000
3587 710000.00000000
3808 100000.00000000
3823 210000.00000000
3856 325000.00000000
3861 135000.00000000
3866 165000.00000000
3868 220000.00000000
3870 475000.00000000
3884 275000.00000000
3899 365000.00000000