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 (
    9984, 9985, 9986, 9987, 9988, 9989, 9990, 
    9991, 9992, 9993, 9994, 9995, 9996, 
    9997, 9998, 9999, 10747, 10748, 10749, 
    10750, 10751, 10752, 9972, 9973, 9975, 
    9976, 9977, 9978, 9979, 10179, 10180, 
    10523
  ) 
  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.00088

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 (9984,9985,9986,9987,9988,9989,9990,9991,9992,9993,9994,9995,9996,9997,9998,9999,10747,10748,10749,10750,10751,10752,9972,9973,9975,9976,9977,9978,9979,10179,10180,10523)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9972 582.40000000
9973 582.40000000
9975 582.40000000
9976 582.40000000
9977 582.40000000
9978 582.40000000
9979 582.40000000
9984 1241.41000000
9985 1241.41000000
9986 1241.41000000
9987 1241.41000000
9988 1241.41000000
9989 1241.41000000
9990 1241.41000000
9991 1241.41000000
9992 1241.41000000
9993 1241.41000000
9994 1241.41000000
9995 1241.41000000
9996 1241.41000000
9997 1241.41000000
9998 1241.41000000
9999 1241.41000000
10179 1660.30000000
10180 1660.30000000
10523 1660.30000000
10747 6184.31000000
10748 6184.31000000
10749 6184.31000000
10750 6184.31000000
10751 6184.31000000
10752 6184.31000000