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 (
    9849, 9850, 9853, 9842, 9851, 9854, 10263, 
    10264, 10265, 10266, 10267, 10268, 
    10269, 10270, 10271, 10272, 10273, 
    10478, 9883, 9901, 10493, 10485, 10476, 
    10479, 10494, 10492, 10468, 9903, 10487, 
    9899, 10473, 10486
  ) 
  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.00091

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 (9849,9850,9853,9842,9851,9854,10263,10264,10265,10266,10267,10268,10269,10270,10271,10272,10273,10478,9883,9901,10493,10485,10476,10479,10494,10492,10468,9903,10487,9899,10473,10486)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9842 2150.58000000
9849 2150.58000000
9850 2150.58000000
9851 2150.58000000
9853 2150.58000000
9854 2150.58000000
9883 1036.29000000
9899 1036.29000000
9901 1036.29000000
9903 1036.29000000
10263 2150.58000000
10264 2150.58000000
10265 2150.58000000
10266 2150.58000000
10267 2150.58000000
10268 2150.58000000
10269 2150.58000000
10270 2150.58000000
10271 2150.58000000
10272 2150.58000000
10273 2150.58000000
10468 1036.29000000
10473 1036.29000000
10476 1036.29000000
10478 1036.29000000
10479 1036.29000000
10485 1036.29000000
10486 1036.29000000
10487 1036.29000000
10492 1036.29000000
10493 1036.29000000
10494 1036.29000000