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 (
    10734, 10735, 10736, 10737, 10738, 10668, 
    10669, 10670, 10671, 10672, 10673, 
    10674, 10675, 10676, 10677, 10678, 
    10679, 10680, 10681, 10682, 10683, 
    10684, 10685, 10686, 10607, 10608, 
    10609, 10610, 10611, 10612, 10613, 
    10614, 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.00188

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 (10734,10735,10736,10737,10738,10668,10669,10670,10671,10672,10673,10674,10675,10676,10677,10678,10679,10680,10681,10682,10683,10684,10685,10686,10607,10608,10609,10610,10611,10612,10613,10614,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
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
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
10668 1136.58000000
10669 1136.58000000
10670 1136.58000000
10671 1136.58000000
10672 1136.58000000
10673 1136.58000000
10674 1136.58000000
10675 1136.58000000
10676 1136.58000000
10677 1136.58000000
10678 1136.58000000
10679 1136.58000000
10680 1136.58000000
10681 1136.58000000
10682 1136.58000000
10683 1136.58000000
10684 1136.58000000
10685 1136.58000000
10686 1136.58000000
10734 2294.40000000
10735 2294.40000000
10736 2294.40000000
10737 2294.40000000
10738 2294.40000000
15732 1136.58000000