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 (
    10818, 10817, 10816, 10815, 10814, 10813, 
    13044, 13043, 13042, 13041, 13040, 
    13039, 13038, 13037, 13036, 13035, 
    13034, 13033, 13032, 13031, 13030, 
    13029, 13028, 13027, 13026, 13025, 
    13024, 13023, 13022, 13021, 13020, 
    13019
  ) 
  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.00056

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 (10818,10817,10816,10815,10814,10813,13044,13043,13042,13041,13040,13039,13038,13037,13036,13035,13034,13033,13032,13031,13030,13029,13028,13027,13026,13025,13024,13023,13022,13021,13020,13019)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
10813 1704.87000000
10814 1704.87000000
10815 1704.87000000
10816 1704.87000000
10817 1704.87000000
10818 1704.87000000
13019 1927.73000000
13020 1927.73000000
13021 1927.73000000
13022 1927.73000000
13023 1927.73000000
13024 1927.73000000
13025 1927.73000000
13026 1927.73000000
13027 1927.73000000
13028 1927.73000000
13029 1927.73000000
13030 1927.73000000
13031 2106.01000000
13032 2106.01000000
13033 2106.01000000
13034 2106.01000000
13035 2106.01000000
13036 2106.01000000
13037 2106.01000000
13038 2106.01000000
13039 2106.01000000
13040 2106.01000000
13041 2106.01000000
13042 2106.01000000
13043 2106.01000000
13044 2106.01000000