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 (
    10390, 10389, 10388, 10387, 10386, 10384, 
    10383, 10382, 12679, 12678, 12677, 
    12676, 12675, 12674, 12673, 15779, 
    12672, 12671, 12994, 12993, 12992, 
    12991, 12990, 12989, 12988, 12987, 
    12986, 12985, 10343, 10345, 10346, 
    10347
  ) 
  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.00086

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 (10390,10389,10388,10387,10386,10384,10383,10382,12679,12678,12677,12676,12675,12674,12673,15779,12672,12671,12994,12993,12992,12991,12990,12989,12988,12987,12986,12985,10343,10345,10346,10347)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
10343 2121.60000000
10345 2121.60000000
10346 2121.60000000
10347 2121.60000000
10382 4944.00000000
10383 4944.00000000
10384 4944.00000000
10386 2540.59000000
10387 2540.59000000
10388 2540.59000000
10389 2540.59000000
10390 2540.59000000
12671 921.36000000
12672 921.36000000
12673 921.36000000
12674 921.36000000
12675 921.36000000
12676 921.36000000
12677 921.36000000
12678 921.36000000
12679 921.36000000
12985 921.36000000
12986 921.36000000
12987 921.36000000
12988 921.36000000
12989 921.36000000
12990 921.36000000
12991 921.36000000
12992 921.36000000
12993 921.36000000
12994 921.36000000
15779 921.36000000