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 (
    12551, 12544, 12546, 12547, 12548, 12549, 
    12550, 12555, 12556, 12557, 12558, 
    12559, 12576, 12577, 12578, 9683, 9685, 
    9686, 9687, 9688, 9689, 9690, 12560, 
    12561, 9775, 9776, 9777, 9778, 9779, 
    9780, 9781, 9782
  ) 
  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.00745

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 (12551,12544,12546,12547,12548,12549,12550,12555,12556,12557,12558,12559,12576,12577,12578,9683,9685,9686,9687,9688,9689,9690,12560,12561,9775,9776,9777,9778,9779,9780,9781,9782)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9683 4000.00000000
9685 4000.00000000
9686 4000.00000000
9687 4000.00000000
9688 4000.00000000
9689 4000.00000000
9690 4000.00000000
9775 1292.58000000
9776 1292.58000000
9777 1292.58000000
9778 1292.58000000
9779 1292.58000000
9780 1292.58000000
9781 1292.58000000
9782 1292.58000000
12544 41728.72000000
12546 67629.15000000
12547 67629.15000000
12548 52280.84000000
12549 43647.16000000
12550 47964.00000000
12551 15215.00000000
12555 41728.72000000
12556 41728.72000000
12557 52280.84000000
12558 82018.56000000
12559 48683.54000000
12560 67629.15000000
12561 67629.15000000
12576 41728.72000000
12577 42208.28000000
12578 36540.00000000