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 (
    5400, 5401, 5402, 5403, 5404, 5405, 5406, 
    5407, 5408, 5409, 5410, 5411, 5412, 
    5413, 5414, 6728, 515, 3156, 3676, 5131, 
    1654, 1655, 1656, 1657, 1658, 1659, 
    1660, 1661, 1662, 1663, 1664, 1665, 
    1666, 1667, 1668, 1669, 1670, 1671, 
    2220, 2221, 2222, 2223, 5353, 5354, 
    5355, 5356, 5357, 5358, 5359, 5360, 
    5361, 5362, 5363, 5364, 5365, 5366, 
    5367, 5368, 5369, 5370, 5820, 5821, 
    5822, 5823
  ) 
  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.00106

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "44.81"
    },
    "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": 64,
        "rows_produced_per_join": 12,
        "filtered": "20.00",
        "index_condition": "(`gaseus`.`cscart_product_prices`.`product_id` in (5400,5401,5402,5403,5404,5405,5406,5407,5408,5409,5410,5411,5412,5413,5414,6728,515,3156,3676,5131,1654,1655,1656,1657,1658,1659,1660,1661,1662,1663,1664,1665,1666,1667,1668,1669,1670,1671,2220,2221,2222,2223,5353,5354,5355,5356,5357,5358,5359,5360,5361,5362,5363,5364,5365,5366,5367,5368,5369,5370,5820,5821,5822,5823))",
        "cost_info": {
          "read_cost": "43.53",
          "eval_cost": "1.28",
          "prefix_cost": "44.81",
          "data_read_per_join": "307"
        },
        "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
515 0.00000000
1654 0.00000000
1655 0.00000000
1656 0.00000000
1657 0.00000000
1658 0.00000000
1659 0.00000000
1660 0.00000000
1661 0.00000000
1662 0.00000000
1663 0.00000000
1664 0.00000000
1665 0.00000000
1666 0.00000000
1667 0.00000000
1668 0.00000000
1669 0.00000000
1670 0.00000000
1671 0.00000000
2220 0.00000000
2221 0.00000000
2222 0.00000000
2223 0.00000000
3156 772.00000000
3676 0.00000000
5131 0.00000000
5353 0.00000000
5354 0.00000000
5355 0.00000000
5356 0.00000000
5357 0.00000000
5358 0.00000000
5359 0.00000000
5360 0.00000000
5361 0.00000000
5362 0.00000000
5363 0.00000000
5364 0.00000000
5365 0.00000000
5366 0.00000000
5367 0.00000000
5368 0.00000000
5369 0.00000000
5370 0.00000000
5400 0.00000000
5401 0.00000000
5402 0.00000000
5403 0.00000000
5404 0.00000000
5405 0.00000000
5406 0.00000000
5407 0.00000000
5408 0.00000000
5409 0.00000000
5410 0.00000000
5411 0.00000000
5412 0.00000000
5413 0.00000000
5414 0.00000000
5820 0.00000000
5821 0.00000000
5822 0.00000000
5823 0.00000000
6728 772.00000000