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 (
    9814, 9815, 9816, 9817, 9818, 15732, 
    9820, 9821, 9822, 9823, 9824, 9825, 
    9826, 9827, 9828, 9829, 9830, 10241, 
    10242, 10243, 10244, 10245, 10246, 
    10247, 10248, 10249, 10250, 10251, 
    10252, 10253, 10254, 10255
  ) 
  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.00093

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 (9814,9815,9816,9817,9818,15732,9820,9821,9822,9823,9824,9825,9826,9827,9828,9829,9830,10241,10242,10243,10244,10245,10246,10247,10248,10249,10250,10251,10252,10253,10254,10255)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9814 1136.58000000
9815 1136.58000000
9816 1136.58000000
9817 1136.58000000
9818 1136.58000000
9820 1136.58000000
9821 1136.58000000
9822 1136.58000000
9823 1136.58000000
9824 1136.58000000
9825 1136.58000000
9826 1136.58000000
9827 1136.58000000
9828 1136.58000000
9829 1136.58000000
9830 1136.58000000
10241 1136.58000000
10242 1136.58000000
10243 1136.58000000
10244 1136.58000000
10245 1136.58000000
10246 1136.58000000
10247 1136.58000000
10248 1136.58000000
10249 1136.58000000
10250 1136.58000000
10251 1136.58000000
10252 1136.58000000
10253 1136.58000000
10254 1136.58000000
10255 1136.58000000
15732 1136.58000000