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 (
    10785, 10786, 10787, 10788, 10789, 10790, 
    10791, 10767, 10768, 10769, 10770, 
    10771, 10772, 10773, 10774, 10775, 
    10776, 10777, 12944, 12985, 12986, 
    12987, 12988, 12989, 12990, 12991, 
    12992, 12993, 12994, 10063, 10052, 
    10072
  ) 
  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.00057

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 (10785,10786,10787,10788,10789,10790,10791,10767,10768,10769,10770,10771,10772,10773,10774,10775,10776,10777,12944,12985,12986,12987,12988,12989,12990,12991,12992,12993,12994,10063,10052,10072)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
10052 1929.60000000
10063 1929.60000000
10072 1929.60000000
10767 5972.60000000
10768 5972.60000000
10769 5972.60000000
10770 5972.60000000
10771 5972.60000000
10772 5972.60000000
10773 5972.60000000
10774 5972.60000000
10775 5972.60000000
10776 5972.60000000
10777 5972.60000000
10785 2106.01000000
10786 2106.01000000
10787 2106.01000000
10788 2106.01000000
10789 2106.01000000
10790 2106.01000000
10791 2106.01000000
12944 1560.00000000
12985 921.36000000
12986 921.36000000
12987 921.36000000
12988 921.36000000
12989 921.36000000
12990 921.36000000
12991 921.36000000
12992 921.36000000
12993 921.36000000
12994 921.36000000