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 (
    1626, 2281, 2282, 2283, 2284, 2285, 2331, 
    2332, 2333, 2334, 2335, 2403, 2404, 
    2405, 2406, 2407, 2408, 2409, 2410, 
    2411, 2599, 2600, 2601, 2602, 5881, 
    5882, 5883, 5884, 5931, 5932, 5933, 
    5934, 6003, 6004, 6005, 6006, 6007, 
    6008, 6009, 6010, 6011, 6192, 6193, 
    6194, 6195, 2595, 2596, 2597, 2598, 
    6187, 6188, 6189, 6190, 2730, 2731, 
    2732, 2733, 2734, 6318, 6319, 6320, 
    6321, 2726, 2727, 2728, 2729, 6313, 
    6314, 6315, 6316, 6317, 1573, 1574, 
    1575, 1576, 1577, 1578, 1579, 1580, 
    1581, 1582, 1583, 1584, 1585, 1586, 
    1587, 1588, 1589, 1590, 1591, 1592, 
    1593, 1594, 1595, 1596, 1597
  ) 
  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.00149

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "67.21"
    },
    "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": 96,
        "rows_produced_per_join": 19,
        "filtered": "20.00",
        "index_condition": "(`gaseus`.`cscart_product_prices`.`product_id` in (1626,2281,2282,2283,2284,2285,2331,2332,2333,2334,2335,2403,2404,2405,2406,2407,2408,2409,2410,2411,2599,2600,2601,2602,5881,5882,5883,5884,5931,5932,5933,5934,6003,6004,6005,6006,6007,6008,6009,6010,6011,6192,6193,6194,6195,2595,2596,2597,2598,6187,6188,6189,6190,2730,2731,2732,2733,2734,6318,6319,6320,6321,2726,2727,2728,2729,6313,6314,6315,6316,6317,1573,1574,1575,1576,1577,1578,1579,1580,1581,1582,1583,1584,1585,1586,1587,1588,1589,1590,1591,1592,1593,1594,1595,1596,1597))",
        "cost_info": {
          "read_cost": "65.29",
          "eval_cost": "1.92",
          "prefix_cost": "67.21",
          "data_read_per_join": "460"
        },
        "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
1573 0.00000000
1574 0.00000000
1575 0.00000000
1576 0.00000000
1577 0.00000000
1578 0.00000000
1579 0.00000000
1580 0.00000000
1581 0.00000000
1582 0.00000000
1583 0.00000000
1584 0.00000000
1585 0.00000000
1586 0.00000000
1587 0.00000000
1588 0.00000000
1589 0.00000000
1590 0.00000000
1591 0.00000000
1592 0.00000000
1593 0.00000000
1594 0.00000000
1595 0.00000000
1596 0.00000000
1597 0.00000000
1626 0.00000000
2281 0.00000000
2282 0.00000000
2283 0.00000000
2284 0.00000000
2285 0.00000000
2331 0.00000000
2332 0.00000000
2333 0.00000000
2334 0.00000000
2335 0.00000000
2403 0.00000000
2404 0.00000000
2405 0.00000000
2406 0.00000000
2407 0.00000000
2408 0.00000000
2409 0.00000000
2410 0.00000000
2411 0.00000000
2595 0.00000000
2596 0.00000000
2597 0.00000000
2598 0.00000000
2599 0.00000000
2600 0.00000000
2601 0.00000000
2602 0.00000000
2726 0.00000000
2727 0.00000000
2728 0.00000000
2729 0.00000000
2730 0.00000000
2731 0.00000000
2732 0.00000000
2733 0.00000000
2734 0.00000000
5881 0.00000000
5882 0.00000000
5883 0.00000000
5884 0.00000000
5931 0.00000000
5932 0.00000000
5933 0.00000000
5934 0.00000000
6003 0.00000000
6004 0.00000000
6005 0.00000000
6006 0.00000000
6007 0.00000000
6008 0.00000000
6009 0.00000000
6010 0.00000000
6011 0.00000000
6187 0.00000000
6188 0.00000000
6189 0.00000000
6190 0.00000000
6192 0.00000000
6193 0.00000000
6194 0.00000000
6195 0.00000000
6313 0.00000000
6314 0.00000000
6315 0.00000000
6316 0.00000000
6317 0.00000000
6318 0.00000000
6319 0.00000000
6320 0.00000000
6321 0.00000000