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 (
    1615, 1616, 1617, 1618, 1619, 1620, 1621, 
    1622, 1623, 1624, 1625, 5299, 5300, 
    5301, 5302, 5303, 5304, 5305, 5306, 
    5307, 5308, 5309, 5310, 5311, 5312, 
    5313, 5314, 5315, 5316, 5317, 5318, 
    5319, 5320, 5321, 5322, 5323, 5324, 
    5325, 1627, 1628, 1629, 1630, 1631, 
    1632, 1633, 1634, 1635, 1636
  ) 
  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.00094

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "34.06"
    },
    "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": 49,
        "rows_produced_per_join": 9,
        "filtered": "20.00",
        "index_condition": "(`gaseus`.`cscart_product_prices`.`product_id` in (1615,1616,1617,1618,1619,1620,1621,1622,1623,1624,1625,5299,5300,5301,5302,5303,5304,5305,5306,5307,5308,5309,5310,5311,5312,5313,5314,5315,5316,5317,5318,5319,5320,5321,5322,5323,5324,5325,1627,1628,1629,1630,1631,1632,1633,1634,1635,1636))",
        "cost_info": {
          "read_cost": "33.08",
          "eval_cost": "0.98",
          "prefix_cost": "34.06",
          "data_read_per_join": "235"
        },
        "used_columns": [
          "product_id",
          "price",
          "percentage_discount",
          "lower_limit",
          "usergroup_id"
        ],
        "attached_condition": "((`gaseus`.`cscart_product_prices`.`lower_limit` = 1) and (`gaseus`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
      }
    }
  }
}

Result

product_id price
1615 0.00000000
1616 0.00000000
1617 0.00000000
1618 0.00000000
1619 0.00000000
1620 0.00000000
1621 0.00000000
1622 0.00000000
1623 0.00000000
1624 0.00000000
1625 0.00000000
1627 0.00000000
1628 0.00000000
1629 0.00000000
1630 0.00000000
1631 0.00000000
1632 0.00000000
1633 0.00000000
1634 0.00000000
1635 0.00000000
1636 0.00000000
5299 0.00000000
5300 0.00000000
5301 0.00000000
5302 0.00000000
5303 0.00000000
5304 0.00000000
5305 0.00000000
5306 0.00000000
5307 0.00000000
5308 0.00000000
5309 0.00000000
5310 0.00000000
5311 0.00000000
5312 0.00000000
5313 0.00000000
5314 0.00000000
5315 0.00000000
5316 0.00000000
5317 0.00000000
5318 0.00000000
5319 0.00000000
5320 0.00000000
5321 0.00000000
5322 0.00000000
5323 0.00000000
5324 0.00000000
5325 0.00000000