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 (
    10615, 10614, 10613, 10612, 10611, 10610, 
    10609, 10608, 10607, 10600, 10599, 
    10598, 10597, 10596, 15952, 15951, 
    15950, 15949, 15947, 15946, 15945, 
    15944, 10583, 10582, 10581, 10580, 
    10579, 10578, 10577, 10576, 10575, 
    10573, 10572, 10569, 10568, 10567, 
    10566, 10565, 10563, 10561, 10560, 
    10558, 10557, 10556, 10552, 10551, 
    10550, 15943, 10545, 10544, 10543, 
    10542, 10541, 10540, 10539, 10538, 
    10537, 10536, 10535, 10534, 10533, 
    10532, 10531, 10530
  ) 
  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.00138

JSON explain

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "cscart_product_prices",
      "access_type": "range",
      "possible_keys": ["usergroup", "product_id", "lower_limit", "usergroup_id"],
      "key": "product_id",
      "key_length": "3",
      "used_key_parts": ["product_id"],
      "rows": 64,
      "filtered": 99.23484039,
      "index_condition": "cscart_product_prices.product_id in (10615,10614,10613,10612,10611,10610,10609,10608,10607,10600,10599,10598,10597,10596,15952,15951,15950,15949,15947,15946,15945,15944,10583,10582,10581,10580,10579,10578,10577,10576,10575,10573,10572,10569,10568,10567,10566,10565,10563,10561,10560,10558,10557,10556,10552,10551,10550,15943,10545,10544,10543,10542,10541,10540,10539,10538,10537,10536,10535,10534,10533,10532,10531,10530)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
10530 1426.30000000
10531 1426.30000000
10532 1426.30000000
10533 1426.30000000
10534 1426.30000000
10535 1426.30000000
10536 1426.30000000
10537 1426.30000000
10538 1426.30000000
10539 1426.30000000
10540 1426.30000000
10541 1426.30000000
10542 1426.30000000
10543 1426.30000000
10544 1426.30000000
10545 2143.00000000
10550 1995.00000000
10551 1995.00000000
10552 2143.00000000
10556 2658.00000000
10557 2658.00000000
10558 2658.00000000
10560 2098.00000000
10561 2098.00000000
10563 2039.00000000
10565 2691.00000000
10566 2089.00000000
10567 2691.00000000
10568 2691.00000000
10569 2325.00000000
10572 2325.00000000
10573 2369.00000000
10575 2210.00000000
10576 1815.00000000
10577 2041.00000000
10578 2205.00000000
10579 1791.00000000
10580 1791.00000000
10581 2142.00000000
10582 1754.00000000
10583 1754.00000000
10596 3365.16000000
10597 3365.16000000
10598 3365.16000000
10599 3365.16000000
10600 3365.16000000
10607 1292.58000000
10608 1292.58000000
10609 1292.58000000
10610 1292.58000000
10611 1292.58000000
10612 1292.58000000
10613 1292.58000000
10614 1292.58000000
10615 1292.58000000
15943 6390.00000000
15944 6875.00000000
15945 6890.00000000
15946 6476.00000000
15947 6047.00000000
15949 4982.00000000
15950 4982.00000000
15951 7167.00000000
15952 3852.00000000