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 (
    1623, 1614, 1604, 1801, 1802, 1724, 1727, 
    1726, 1599, 1725, 1600, 1872, 1603, 
    1513, 1550, 1560, 1548, 1547, 1763, 
    1764, 1506, 1735, 1602, 1601, 1692, 
    1696, 1682, 1743, 1556, 1555, 1554, 
    1553
  ) 
  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": 6.530610561,
      "index_condition": "cscart_product_prices.product_id in (1623,1614,1604,1801,1802,1724,1727,1726,1599,1725,1600,1872,1603,1513,1550,1560,1548,1547,1763,1764,1506,1735,1602,1601,1692,1696,1682,1743,1556,1555,1554,1553)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
1506 24500.00000000
1513 27500.00000000
1547 27500.00000000
1548 27500.00000000
1550 27500.00000000
1553 20000.00000000
1554 20000.00000000
1555 0.00000000
1556 0.00000000
1560 0.00000000
1599 22000.00000000
1600 24000.00000000
1601 24500.00000000
1602 24500.00000000
1603 52000.00000000
1604 70000.00000000
1614 70000.00000000
1623 0.00000000
1682 0.00000000
1692 30000.00000000
1696 52000.00000000
1724 22000.00000000
1725 22000.00000000
1726 22000.00000000
1727 22000.00000000
1735 24500.00000000
1743 0.00000000
1763 24500.00000000
1764 24500.00000000
1801 37000.00000000
1802 35000.00000000
1872 0.00000000