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 (
    9804, 9809, 9810, 9811, 9812, 9807, 9808, 
    9683, 10455, 10456, 10454, 10453, 10452, 
    10451, 10450, 10449, 10448, 10447, 
    12691, 12692, 12693, 12694, 12695, 
    12696, 12697, 12698, 12699, 10370, 
    10372, 10374, 10376, 10377
  ) 
  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.00093

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 (9804,9809,9810,9811,9812,9807,9808,9683,10455,10456,10454,10453,10452,10451,10450,10449,10448,10447,12691,12692,12693,12694,12695,12696,12697,12698,12699,10370,10372,10374,10376,10377)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9683 4000.00000000
9804 1292.58000000
9807 1292.58000000
9808 1292.58000000
9809 1292.58000000
9810 1292.58000000
9811 1292.58000000
9812 1292.58000000
10370 2763.44000000
10372 2763.44000000
10374 2763.44000000
10376 2763.44000000
10377 2763.44000000
10447 1894.30000000
10448 1894.30000000
10449 1894.30000000
10450 1894.30000000
10451 1894.30000000
10452 1894.30000000
10453 1894.30000000
10454 1894.30000000
10455 1894.30000000
10456 1894.30000000
12691 1894.30000000
12692 1894.30000000
12693 1894.30000000
12694 1894.30000000
12695 1894.30000000
12696 1894.30000000
12697 1894.30000000
12698 1894.30000000
12699 1894.30000000