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 (
    10027, 10028, 10029, 10030, 10031, 10032, 
    10033, 10034, 10035, 10036, 10037, 
    10038, 10039, 10040, 9984, 9985, 9986, 
    9987, 9988, 9989, 9990, 9991, 9992, 
    9993, 9994, 9995, 9996, 9997, 9998, 
    9999, 9972, 9973
  ) 
  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.00390

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 (10027,10028,10029,10030,10031,10032,10033,10034,10035,10036,10037,10038,10039,10040,9984,9985,9986,9987,9988,9989,9990,9991,9992,9993,9994,9995,9996,9997,9998,9999,9972,9973)",
      "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
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
10027 1716.01000000
10028 1716.01000000
10029 1716.01000000
10030 1716.01000000
10031 1716.01000000
10032 1716.01000000
10033 1716.01000000
10034 1716.01000000
10035 1716.01000000
10036 1716.01000000
10037 1716.01000000
10038 1716.01000000
10039 1716.01000000
10040 1716.01000000