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 (
    9730, 9731, 9732, 9733, 10181, 9734, 
    9735, 9724, 10655, 10654, 10605, 10604, 
    9873, 10653, 10646, 10645, 10647, 10648, 
    10649, 10726, 10650, 9789, 10547, 9796, 
    9874, 9875, 9879, 9887, 9941, 9942, 
    9943, 9944
  ) 
  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.00090

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 (9730,9731,9732,9733,10181,9734,9735,9724,10655,10654,10605,10604,9873,10653,10646,10645,10647,10648,10649,10726,10650,9789,10547,9796,9874,9875,9879,9887,9941,9942,9943,9944)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9724 1482.01000000
9730 1482.01000000
9731 1482.01000000
9732 1482.01000000
9733 1482.01000000
9734 1482.01000000
9735 1482.01000000
9789 1381.72000000
9796 1704.87000000
9873 2997.44000000
9874 1704.87000000
9875 1704.87000000
9879 1704.87000000
9887 1704.87000000
9941 1704.87000000
9942 1704.87000000
9943 1704.87000000
9944 1704.87000000
10181 1482.01000000
10547 1381.72000000
10604 2997.44000000
10605 2997.44000000
10645 1381.72000000
10646 1381.72000000
10647 1381.72000000
10648 1381.72000000
10649 1381.72000000
10650 1381.72000000
10653 1381.72000000
10654 2997.44000000
10655 2997.44000000
10726 1381.72000000