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, 9876, 9877, 
    9878, 9880, 9881, 9882, 9883, 9885, 
    9886, 9888, 9889, 9890, 9891, 9892, 
    9893, 9894, 9895, 9896, 9897, 9899, 
    9900, 9901, 9903, 9838, 9839, 9840, 
    9841
  ) 
  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.00087

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,9876,9877,9878,9880,9881,9882,9883,9885,9886,9888,9889,9890,9891,9892,9893,9894,9895,9896,9897,9899,9900,9901,9903,9838,9839,9840,9841)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9838 2150.58000000
9839 2150.58000000
9840 2150.58000000
9841 2150.58000000
9876 1036.29000000
9877 1036.29000000
9878 1036.29000000
9880 1036.29000000
9881 1036.29000000
9882 1036.29000000
9883 1036.29000000
9885 1036.29000000
9886 1036.29000000
9888 1036.29000000
9889 1036.29000000
9890 1036.29000000
9891 1036.29000000
9892 1036.29000000
9893 1036.29000000
9894 1036.29000000
9895 1036.29000000
9896 1036.29000000
9897 1036.29000000
9899 1036.29000000
9900 1036.29000000
9901 1036.29000000
9903 1036.29000000
9975 582.40000000
9976 582.40000000
9977 582.40000000
9978 582.40000000
9979 582.40000000