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 (
    9846, 9845, 9860, 9859, 9858, 9857, 9852, 
    9861, 9862, 9847, 9863, 9856, 9855, 
    9838, 9849, 9850, 9853, 9842, 9851, 
    9854, 10263, 10264, 10265, 10266, 10267, 
    10268, 10269, 10270, 10271, 10272, 
    10273, 15943
  ) 
  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.00057

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 (9846,9845,9860,9859,9858,9857,9852,9861,9862,9847,9863,9856,9855,9838,9849,9850,9853,9842,9851,9854,10263,10264,10265,10266,10267,10268,10269,10270,10271,10272,10273,15943)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9838 2150.58000000
9842 2150.58000000
9845 2150.58000000
9846 2150.58000000
9847 2150.58000000
9849 2150.58000000
9850 2150.58000000
9851 2150.58000000
9852 2150.58000000
9853 2150.58000000
9854 2150.58000000
9855 2150.58000000
9856 2150.58000000
9857 2150.58000000
9858 2150.58000000
9859 2150.58000000
9860 2150.58000000
9861 2150.58000000
9862 2150.58000000
9863 2150.58000000
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
15943 6390.00000000