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, 
    10792, 10793, 10794, 10795, 10796, 
    10797, 10798, 10799, 10800, 10801, 
    10802, 10803, 10804, 10805, 10806, 
    10807
  ) 
  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.00363

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,10792,10793,10794,10795,10796,10797,10798,10799,10800,10801,10802,10803,10804,10805,10806,10807)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
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
10792 1582.30000000
10793 1582.30000000
10794 1582.30000000
10795 1582.30000000
10796 1582.30000000
10797 1582.30000000
10798 1582.30000000
10799 1582.30000000
10800 1582.30000000
10801 1582.30000000
10802 1582.30000000
10803 1582.30000000
10804 1582.30000000
10805 1582.30000000
10806 1582.30000000
10807 1582.30000000