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 (
    1584, 1564, 1583, 5939, 5918, 3935, 5961, 
    1566, 5925, 3938, 1580, 2277, 3930, 
    2283, 2281, 1581, 1582, 2279, 2284, 
    2280, 3955, 2282, 2285, 3931, 3960, 
    3954, 3934, 3961, 4006, 3958, 3933, 
    4005, 3962, 3963, 5249, 3957, 4002, 
    5198, 5915, 5960
  ) 
  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.00140

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "28.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": 40,
        "rows_produced_per_join": 7,
        "filtered": "19.99",
        "index_condition": "(`softwarepirmam_hewadelivard_cscart_4`.`cscart_product_prices`.`product_id` in (1584,1564,1583,5939,5918,3935,5961,1566,5925,3938,1580,2277,3930,2283,2281,1581,1582,2279,2284,2280,3955,2282,2285,3931,3960,3954,3934,3961,4006,3958,3933,4005,3962,3963,5249,3957,4002,5198,5915,5960))",
        "cost_info": {
          "read_cost": "27.21",
          "eval_cost": "0.80",
          "prefix_cost": "28.01",
          "data_read_per_join": "191"
        },
        "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
1564 555000.00000000
1566 529000.00000000
1580 320000.00000000
1581 330000.00000000
1582 200000.00000000
1583 829000.00000000
1584 207000.00000000
2277 60000.00000000
2279 246000.00000000
2280 48000.00000000
2281 45000.00000000
2282 19000.00000000
2283 63000.00000000
2284 28000.00000000
2285 36000.00000000
3930 42000.00000000
3931 54000.00000000
3933 60000.00000000
3934 60000.00000000
3935 60000.00000000
3938 66000.00000000
3954 72000.00000000
3955 102000.00000000
3957 108000.00000000
3958 60000.00000000
3960 102000.00000000
3961 102000.00000000
3962 150000.00000000
3963 162000.00000000
4002 240000.00000000
4005 240000.00000000
4006 282000.00000000
5198 702000.00000000
5249 900000.00000000
5915 396000.00000000
5918 118000.00000000
5925 432000.00000000
5939 86000.00000000
5960 540000.00000000
5961 316000.00000000