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 (
    12995, 12996, 12997, 12998, 15744, 15745, 
    15746, 15743, 15738, 15739, 15740, 
    15741, 15742, 15736, 15737, 12999, 
    13000, 13001, 13002, 13003, 13004, 
    13005, 13006, 13007, 13008, 13009, 
    13010, 13011, 13012, 12969, 13013, 
    13014
  ) 
  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 (12995,12996,12997,12998,15744,15745,15746,15743,15738,15739,15740,15741,15742,15736,15737,12999,13000,13001,13002,13003,13004,13005,13006,13007,13008,13009,13010,13011,13012,12969,13013,13014)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
12969 1606.00000000
12995 3186.87000000
12996 3186.87000000
12997 3186.87000000
12998 3186.87000000
12999 2061.44000000
13000 2061.44000000
13001 2061.44000000
13002 2061.44000000
13003 2061.44000000
13004 2061.44000000
13005 2061.44000000
13006 2061.44000000
13007 2061.44000000
13008 2061.44000000
13009 2061.44000000
13010 2061.44000000
13011 2061.44000000
13012 2061.44000000
13013 1927.73000000
13014 1927.73000000
15736 3955.73000000
15737 3955.73000000
15738 3955.73000000
15739 3955.73000000
15740 3955.73000000
15741 3955.73000000
15742 3955.73000000
15743 3955.73000000
15744 3000.00000000
15745 3955.73000000
15746 3955.73000000