SELECT 
  cscart_product_prices.product_id, 
  COALESCE(
    cscart_master_products_storefront_min_price.price, 
    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 
  LEFT JOIN cscart_master_products_storefront_min_price ON cscart_master_products_storefront_min_price.product_id = cscart_product_prices.product_id 
  AND cscart_master_products_storefront_min_price.storefront_id = 1 
WHERE 
  cscart_product_prices.product_id IN (
    4924, 5315, 5446, 5167, 5131, 4488, 4936, 
    5309, 4942, 4943, 21006, 18166, 5447, 
    4850, 5448, 4399, 4442, 4400, 4401, 
    4885, 4672, 4473, 4437, 5353
  ) 
  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.00081

JSON explain

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "cscart_master_products_storefront_min_price",
      "access_type": "system",
      "possible_keys": ["PRIMARY"],
      "rows": 0,
      "filtered": 0,
      "const_row_not_found": true
    },
    "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": 24,
      "filtered": 75,
      "index_condition": "cscart_product_prices.product_id in (4924,5315,5446,5167,5131,4488,4936,5309,4942,4943,21006,18166,5447,4850,5448,4399,4442,4400,4401,4885,4672,4473,4437,5353)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
4399 79990.00000000
4400 79990.00000000
4401 79990.00000000
4437 105990.00000000
4442 79990.00000000
4473 89990.00000000
4488 42990.00000000
4672 89990.00000000
4850 67990.00000000
4885 89990.00000000
4924 42990.00000000
4936 57990.00000000
4942 57990.00000000
4943 57990.00000000
5131 42990.00000000
5167 47990.00000000
5309 57990.00000000
5315 42990.00000000
5353 105990.00000000
5446 37990.00000000
5447 67990.00000000
5448 67990.00000000
18166 0.00000000
21006 69990.00000000