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 (
    1572, 1574, 1592, 4974, 2399, 1576, 1665, 
    1586, 5277, 1593, 5899, 5640, 2201, 
    5934, 5939, 3929, 5918, 1562, 5284, 
    5376, 4193, 1571, 5287, 3226, 5281, 
    3218, 5397, 4995, 5270, 2393, 3464, 
    1575, 5278, 5187, 5549, 3935, 1588, 
    1563, 1570, 3826
  ) 
  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.00320

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 (1572,1574,1592,4974,2399,1576,1665,1586,5277,1593,5899,5640,2201,5934,5939,3929,5918,1562,5284,5376,4193,1571,5287,3226,5281,3218,5397,4995,5270,2393,3464,1575,5278,5187,5549,3935,1588,1563,1570,3826))",
        "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
1562 82500.00000000
1563 178000.00000000
1570 536000.00000000
1571 516000.00000000
1572 558000.00000000
1574 843000.00000000
1575 568000.00000000
1576 656000.00000000
1586 558000.00000000
1588 1385000.00000000
1592 902000.00000000
1593 1020000.00000000
1665 30000.00000000
2201 119000.00000000
2393 13000.00000000
2399 27000.00000000
3218 12000.00000000
3226 15000.00000000
3464 16000.00000000
3826 8000.00000000
3929 42000.00000000
3935 60000.00000000
4193 36000.00000000
4974 269250.00000000
4995 156750.00000000
5187 35000.00000000
5270 8000.00000000
5277 8000.00000000
5278 8000.00000000
5281 8000.00000000
5284 8000.00000000
5287 8000.00000000
5376 825000.00000000
5397 1050000.00000000
5549 422500.00000000
5640 20000.00000000
5899 137000.00000000
5918 118000.00000000
5934 229000.00000000
5939 86000.00000000