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 (
    10419, 10420, 10421, 10422, 10423, 10424, 
    10384, 10383, 10382, 10753, 10754, 
    10755, 10296, 10297, 10298, 10299, 
    10300, 10446, 10063, 10052, 10072, 
    10046, 10059, 10064, 10057, 10048, 
    10062, 10070, 10058, 10045, 10054, 
    10047
  ) 
  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 (10419,10420,10421,10422,10423,10424,10384,10383,10382,10753,10754,10755,10296,10297,10298,10299,10300,10446,10063,10052,10072,10046,10059,10064,10057,10048,10062,10070,10058,10045,10054,10047)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
10045 1672.00000000
10046 1929.60000000
10047 1929.60000000
10048 1929.60000000
10052 1929.60000000
10054 1929.60000000
10057 1929.60000000
10058 1929.60000000
10059 1929.60000000
10062 1929.60000000
10063 1929.60000000
10064 1929.60000000
10070 1929.60000000
10072 1929.60000000
10296 3409.73000000
10297 3409.73000000
10298 3409.73000000
10299 3409.73000000
10300 3409.73000000
10382 4944.00000000
10383 4944.00000000
10384 4944.00000000
10419 1317.94000000
10420 1317.94000000
10421 1317.94000000
10422 1317.94000000
10423 1317.94000000
10424 1317.94000000
10446 3409.73000000
10753 7644.03000000
10754 7644.03000000
10755 7644.03000000