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 (
    4793, 4783, 4790, 18167, 4461, 5285, 
    4464, 4617, 4618, 4619, 5376, 5377, 
    5064, 4905, 5378, 4654, 4655, 4656, 
    5319, 5318, 4675, 4752, 4753, 4435
  ) 
  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.00079

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 (4793,4783,4790,18167,4461,5285,4464,4617,4618,4619,5376,5377,5064,4905,5378,4654,4655,4656,5319,5318,4675,4752,4753,4435)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
4435 19990.00000000
4461 17990.00000000
4464 9990.00000000
4617 12990.00000000
4618 12990.00000000
4619 12990.00000000
4654 16990.00000000
4655 16990.00000000
4656 16990.00000000
4675 21990.00000000
4752 21990.00000000
4753 23990.00000000
4783 27990.00000000
4790 27990.00000000
4793 27990.00000000
4905 14990.00000000
5064 15990.00000000
5285 6990.00000000
5318 15990.00000000
5319 15990.00000000
5376 12990.00000000
5377 12990.00000000
5378 16990.00000000
18167 0.00000000