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 (
    12591, 12582, 12590, 12571, 12632, 12559, 
    12631, 15938, 13091, 12551, 12638, 
    12634, 12636, 12635, 12599, 12627, 
    12600, 12639, 15800, 12595, 12587, 
    12593, 12620, 12623, 12619, 12585, 
    12616, 12601, 12628, 12602, 12640, 
    12647
  ) 
  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.00123

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 (12591,12582,12590,12571,12632,12559,12631,15938,13091,12551,12638,12634,12636,12635,12599,12627,12600,12639,15800,12595,12587,12593,12620,12623,12619,12585,12616,12601,12628,12602,12640,12647)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
12551 15215.00000000
12559 48683.54000000
12571 67629.15000000
12582 41728.72000000
12585 209523.67000000
12587 209523.67000000
12590 67629.15000000
12591 67629.15000000
12593 349146.47000000
12595 131354.16000000
12599 14092.68000000
12600 4597.28000000
12601 14092.68000000
12602 4597.28000000
12616 15852.84000000
12619 131354.16000000
12620 131354.16000000
12623 209523.67000000
12627 21943.06000000
12628 21943.06000000
12631 41728.72000000
12632 67629.15000000
12634 25398.80000000
12635 25398.80000000
12636 46725.90000000
12638 19593.76000000
12639 19593.76000000
12640 19593.76000000
12647 33378.35000000
13091 6750.00000000
15800 33378.35000000
15938 4889.00000000