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 (
    9813, 10095, 9904, 10178, 10644, 10093, 
    10096, 10167, 10525, 10526, 10527, 
    10528, 10529, 10530, 10531, 10532, 
    10533, 10534, 10535, 10536, 10537, 
    10538, 10539, 10540, 10541, 10542, 
    10524, 10543, 10544, 13013, 13014, 
    13015
  ) 
  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.00077

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 (9813,10095,9904,10178,10644,10093,10096,10167,10525,10526,10527,10528,10529,10530,10531,10532,10533,10534,10535,10536,10537,10538,10539,10540,10541,10542,10524,10543,10544,13013,13014,13015)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9813 1849.73000000
9904 1849.73000000
10093 1849.73000000
10095 1849.73000000
10096 1849.73000000
10167 1849.73000000
10178 1849.73000000
10524 1426.30000000
10525 1426.30000000
10526 1426.30000000
10527 1426.30000000
10528 1426.30000000
10529 1426.30000000
10530 1426.30000000
10531 1426.30000000
10532 1426.30000000
10533 1426.30000000
10534 1426.30000000
10535 1426.30000000
10536 1426.30000000
10537 1426.30000000
10538 1426.30000000
10539 1426.30000000
10540 1426.30000000
10541 1426.30000000
10542 1426.30000000
10543 1426.30000000
10544 1426.30000000
10644 1849.73000000
13013 1927.73000000
13014 1927.73000000
13015 1927.73000000