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 (
    10718, 10719, 10720, 10721, 10722, 10723, 
    10739, 10740, 10741, 10742, 10305, 
    10306, 10307, 10308, 10309, 10310, 
    10311, 10312, 12999, 13000, 13001, 
    13002, 13003, 13004, 13005, 13006, 
    13007, 13008, 13009, 13010, 13011, 
    13012
  ) 
  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.00538

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 (10718,10719,10720,10721,10722,10723,10739,10740,10741,10742,10305,10306,10307,10308,10309,10310,10311,10312,12999,13000,13001,13002,13003,13004,13005,13006,13007,13008,13009,13010,13011,13012)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
10305 3225.60000000
10306 3225.60000000
10307 3225.60000000
10308 3225.60000000
10309 3225.60000000
10310 3225.60000000
10311 3225.60000000
10312 3225.60000000
10718 1537.72000000
10719 1537.72000000
10720 1537.72000000
10721 1537.72000000
10722 1537.72000000
10723 1537.72000000
10739 4457.16000000
10740 4457.16000000
10741 4457.16000000
10742 3755.16000000
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