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 (
    9975, 9976, 9977, 9978, 9979, 10179, 
    10180, 10523, 10073, 10077, 10076, 
    10086, 10085, 10084, 10083, 10082, 
    10081, 10080, 10079, 10584, 10594, 
    9814, 9815, 9816, 9817, 9818, 15732, 
    9820, 9821, 9822, 9823, 9824
  ) 
  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.00160

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 (9975,9976,9977,9978,9979,10179,10180,10523,10073,10077,10076,10086,10085,10084,10083,10082,10081,10080,10079,10584,10594,9814,9815,9816,9817,9818,15732,9820,9821,9822,9823,9824)",
      "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
9975 582.40000000
9976 582.40000000
9977 582.40000000
9978 582.40000000
9979 582.40000000
10073 1660.30000000
10076 1660.30000000
10077 1660.30000000
10079 1660.30000000
10080 1660.30000000
10081 1660.30000000
10082 1660.30000000
10083 1660.30000000
10084 1660.30000000
10085 1660.30000000
10086 1660.30000000
10179 1660.30000000
10180 1660.30000000
10523 1660.30000000
10584 1660.30000000
10594 1660.30000000
15732 1136.58000000