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 (
    10734, 10735, 10736, 10737, 10738, 10668, 
    10669, 10670, 10671, 10672, 10673, 
    10674, 10675, 10676, 10677, 10678, 
    10679, 10680, 10681, 10682, 10683, 
    10684, 10685, 10686, 10607, 10608, 
    10609, 10610, 10611, 10612, 10613, 
    10614
  ) 
  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.00077

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 (10734,10735,10736,10737,10738,10668,10669,10670,10671,10672,10673,10674,10675,10676,10677,10678,10679,10680,10681,10682,10683,10684,10685,10686,10607,10608,10609,10610,10611,10612,10613,10614)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
10607 1292.58000000
10608 1292.58000000
10609 1292.58000000
10610 1292.58000000
10611 1292.58000000
10612 1292.58000000
10613 1292.58000000
10614 1292.58000000
10668 1136.58000000
10669 1136.58000000
10670 1136.58000000
10671 1136.58000000
10672 1136.58000000
10673 1136.58000000
10674 1136.58000000
10675 1136.58000000
10676 1136.58000000
10677 1136.58000000
10678 1136.58000000
10679 1136.58000000
10680 1136.58000000
10681 1136.58000000
10682 1136.58000000
10683 1136.58000000
10684 1136.58000000
10685 1136.58000000
10686 1136.58000000
10734 2294.40000000
10735 2294.40000000
10736 2294.40000000
10737 2294.40000000
10738 2294.40000000