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 (
    12498, 13101, 13100, 10699, 10695, 10195, 
    10576, 10696, 9760, 10583, 10701, 10582, 
    10139, 12503, 10575, 9932, 10075, 10135, 
    9952, 10074, 10573, 10577, 10651, 10642, 
    12529, 12515, 12525, 12532, 12523, 
    10198, 10557, 10202
  ) 
  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.00055

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 (12498,13101,13100,10699,10695,10195,10576,10696,9760,10583,10701,10582,10139,12503,10575,9932,10075,10135,9952,10074,10573,10577,10651,10642,12529,12515,12525,12532,12523,10198,10557,10202)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9760 1754.00000000
9932 1563.00000000
9952 2041.00000000
10074 2369.00000000
10075 2369.00000000
10135 2041.00000000
10139 2338.00000000
10195 1815.00000000
10198 2405.00000000
10202 2405.00000000
10557 2658.00000000
10573 2369.00000000
10575 2210.00000000
10576 1815.00000000
10577 2041.00000000
10582 1754.00000000
10583 1754.00000000
10642 2369.00000000
10651 2041.00000000
10695 1848.00000000
10696 1840.00000000
10699 1551.00000000
10701 1840.00000000
12498 0.00000000
12503 0.00000000
12515 0.00000000
12523 0.00000000
12525 0.00000000
12529 0.00000000
12532 0.00000000
13100 0.00000000
13101 0.00000000