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 (
    10246, 10247, 10248, 10249, 10250, 10251, 
    10252, 10253, 10254, 10255, 10256, 
    10257, 10258, 10259, 10260, 10261, 
    10425, 10426, 10427, 10428, 10429, 
    10430, 10431, 10432, 10433, 10434, 
    10435, 10436, 10437, 10438, 10439, 
    10440
  ) 
  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.00132

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 (10246,10247,10248,10249,10250,10251,10252,10253,10254,10255,10256,10257,10258,10259,10260,10261,10425,10426,10427,10428,10429,10430,10431,10432,10433,10434,10435,10436,10437,10438,10439,10440)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
10246 1136.58000000
10247 1136.58000000
10248 1136.58000000
10249 1136.58000000
10250 1136.58000000
10251 1136.58000000
10252 1136.58000000
10253 1136.58000000
10254 1136.58000000
10255 1136.58000000
10256 1136.58000000
10257 1136.58000000
10258 1136.58000000
10259 1136.58000000
10260 1136.58000000
10261 1136.58000000
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