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 (
    10327, 10328, 10329, 10330, 10331, 10332, 
    10333, 10334, 10335, 10336, 10337, 
    10338, 10339, 10340, 10341, 10342, 
    10778, 10779, 10780, 10781, 10782, 
    10783, 10784, 10785, 10786, 10787, 
    10788, 10789, 10790, 10791, 9688, 10792
  ) 
  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.00078

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 (10327,10328,10329,10330,10331,10332,10333,10334,10335,10336,10337,10338,10339,10340,10341,10342,10778,10779,10780,10781,10782,10783,10784,10785,10786,10787,10788,10789,10790,10791,9688,10792)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
9688 4000.00000000
10327 518.70000000
10328 518.70000000
10329 518.70000000
10330 518.70000000
10331 518.70000000
10332 518.70000000
10333 518.70000000
10334 518.70000000
10335 518.70000000
10336 518.70000000
10337 518.70000000
10338 518.70000000
10339 518.70000000
10340 518.70000000
10341 518.70000000
10342 518.70000000
10778 2106.01000000
10779 2106.01000000
10780 2106.01000000
10781 2106.01000000
10782 2106.01000000
10783 2106.01000000
10784 2106.01000000
10785 2106.01000000
10786 2106.01000000
10787 2106.01000000
10788 2106.01000000
10789 2106.01000000
10790 2106.01000000
10791 2106.01000000
10792 1582.30000000