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, 10616, 10617, 10618, 10619, 10620, 
    10621, 10622, 10623, 10624, 9814, 9815, 
    9816, 9817, 9818, 15732, 9820, 9821, 
    9822, 9823, 9824, 9825, 9826, 9827, 
    9828, 9829, 9830, 10241, 10242, 10243, 
    10244, 10245
  ) 
  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.00072

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": 32,
      "filtered": 99.23484039,
      "index_condition": "cscart_product_prices.product_id in (10615,10616,10617,10618,10619,10620,10621,10622,10623,10624,9814,9815,9816,9817,9818,15732,9820,9821,9822,9823,9824,9825,9826,9827,9828,9829,9830,10241,10242,10243,10244,10245)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9814 1136.58000000
9815 1136.58000000
9816 1136.58000000
9817 1136.58000000
9818 1136.58000000
9820 1136.58000000
9821 1136.58000000
9822 1136.58000000
9823 1136.58000000
9824 1136.58000000
9825 1136.58000000
9826 1136.58000000
9827 1136.58000000
9828 1136.58000000
9829 1136.58000000
9830 1136.58000000
10241 1136.58000000
10242 1136.58000000
10243 1136.58000000
10244 1136.58000000
10245 1136.58000000
10615 1292.58000000
10616 1292.58000000
10617 1292.58000000
10618 1292.58000000
10619 1292.58000000
10620 1292.58000000
10621 1292.58000000
10622 1292.58000000
10623 1292.58000000
10624 1292.58000000
15732 1136.58000000