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 (
    10456, 10455, 12702, 10446, 12579, 12580, 
    12700, 10444, 10443, 10442, 10441, 
    10440, 10439, 10438, 10437, 10436, 
    10435, 10434, 10433, 10432, 10431, 
    10430, 10429, 10428, 10427, 10426, 
    10425, 12564, 10424, 10423, 10422, 
    10421
  ) 
  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.00080

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 (10456,10455,12702,10446,12579,12580,12700,10444,10443,10442,10441,10440,10439,10438,10437,10436,10435,10434,10433,10432,10431,10430,10429,10428,10427,10426,10425,12564,10424,10423,10422,10421)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
10421 1317.94000000
10422 1317.94000000
10423 1317.94000000
10424 1317.94000000
10425 1671.44000000
10426 1671.44000000
10427 1671.44000000
10428 1671.44000000
10429 1671.44000000
10430 1671.44000000
10431 1671.44000000
10432 1671.44000000
10433 1671.44000000
10434 1671.44000000
10435 1671.44000000
10436 1671.44000000
10437 1671.44000000
10438 1671.44000000
10439 1671.44000000
10440 1671.44000000
10441 1671.44000000
10442 1671.44000000
10443 1671.44000000
10444 1671.44000000
10446 3409.73000000
10455 1894.30000000
10456 1894.30000000
12564 67629.15000000
12579 10072.36000000
12580 11031.68000000
12700 1326.01000000
12702 2150.58000000