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 (
    10696, 10695, 10693, 10692, 15958, 15957, 
    15956, 12600, 12602, 12604, 12606, 
    12608, 12603, 12615, 12597, 12609, 
    12610, 12607, 12596, 12611, 12613, 
    12612, 12605, 12601, 12616, 12599, 
    10666, 10664, 10663, 10661, 10659, 
    10658
  ) 
  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.00782

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 (10696,10695,10693,10692,15958,15957,15956,12600,12602,12604,12606,12608,12603,12615,12597,12609,12610,12607,12596,12611,12613,12612,12605,12601,12616,12599,10666,10664,10663,10661,10659,10658)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
10658 2671.00000000
10659 2686.00000000
10661 2262.00000000
10663 2262.00000000
10664 2325.00000000
10666 2325.00000000
10692 2671.00000000
10693 2089.00000000
10695 1848.00000000
10696 1840.00000000
12596 45140.72000000
12597 46725.90000000
12599 14092.68000000
12600 4597.28000000
12601 14092.68000000
12602 4597.28000000
12603 14092.68000000
12604 4597.28000000
12605 14092.68000000
12606 4597.28000000
12607 14092.68000000
12608 2296.27000000
12609 14678.09000000
12610 13507.47000000
12611 13507.47000000
12612 15852.84000000
12613 15852.84000000
12615 15852.84000000
12616 15852.84000000
15956 6896.00000000
15957 4709.00000000
15958 5578.00000000