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 (
    10074, 10573, 10577, 10651, 10642, 12529, 
    12515, 12525, 12532, 12523, 10198, 
    10557, 10202, 10556, 10101, 10558, 
    10142, 10134, 10141, 10053, 10138, 
    10130, 9934, 10563, 10569, 10572, 10602, 
    10603, 10137, 12517, 12531, 12526
  ) 
  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.00090

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 (10074,10573,10577,10651,10642,12529,12515,12525,12532,12523,10198,10557,10202,10556,10101,10558,10142,10134,10141,10053,10138,10130,9934,10563,10569,10572,10602,10603,10137,12517,12531,12526)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9934 2039.00000000
10053 1955.00000000
10074 2369.00000000
10101 2405.00000000
10130 2121.00000000
10134 2121.00000000
10137 1924.00000000
10138 1955.00000000
10141 2153.00000000
10142 2121.00000000
10198 2405.00000000
10202 2405.00000000
10556 2658.00000000
10557 2658.00000000
10558 2658.00000000
10563 2039.00000000
10569 2325.00000000
10572 2325.00000000
10573 2369.00000000
10577 2041.00000000
10602 1885.00000000
10603 2517.00000000
10642 2369.00000000
10651 2041.00000000
12515 0.00000000
12517 0.00000000
12523 0.00000000
12525 0.00000000
12526 0.00000000
12529 0.00000000
12531 0.00000000
12532 0.00000000