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 (
    10130, 10603, 10135, 10134, 10139, 10138, 
    10137, 10141, 10143, 10142, 12628, 
    12627, 12629, 10095, 10195, 10194, 
    10193, 10190, 10496, 10198, 10202, 
    10201, 10200, 10227, 10226, 10651, 
    9945, 10642, 10601, 9727, 9728, 9729
  ) 
  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.00050

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 (10130,10603,10135,10134,10139,10138,10137,10141,10143,10142,12628,12627,12629,10095,10195,10194,10193,10190,10496,10198,10202,10201,10200,10227,10226,10651,9945,10642,10601,9727,9728,9729)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9727 1482.01000000
9728 1482.01000000
9729 1482.01000000
9945 1482.01000000
10095 1849.73000000
10130 2121.00000000
10134 2121.00000000
10135 2041.00000000
10137 1924.00000000
10138 1955.00000000
10139 2338.00000000
10141 2153.00000000
10142 2121.00000000
10143 2205.00000000
10190 2257.00000000
10193 2325.00000000
10194 2257.00000000
10195 1815.00000000
10198 2405.00000000
10200 2234.00000000
10201 2234.00000000
10202 2405.00000000
10226 1524.00000000
10227 1997.00000000
10496 1955.00000000
10601 1482.01000000
10603 2517.00000000
10642 2369.00000000
10651 2041.00000000
12627 21943.06000000
12628 21943.06000000
12629 21943.06000000