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 (
    6273, 6430, 6163, 6164, 6274, 6155, 6156, 
    6434, 8899, 6570, 6595, 6571, 6338, 
    6436, 6339, 6077, 6438, 6572, 6590, 
    6593, 6435, 18033, 6573, 18034
  ) 
  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.00090

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 (6273,6430,6163,6164,6274,6155,6156,6434,8899,6570,6595,6571,6338,6436,6339,6077,6438,6572,6590,6593,6435,18033,6573,18034)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
6077 3590.00000000
6155 2790.00000000
6156 2790.00000000
6163 2290.00000000
6164 2290.00000000
6273 2990.00000000
6274 2990.00000000
6338 3590.00000000
6339 3590.00000000
6430 2790.00000000
6434 3290.00000000
6435 3990.00000000
6436 3590.00000000
6438 3990.00000000
6570 3790.00000000
6571 3790.00000000
6572 3990.00000000
6573 4190.00000000
6590 3990.00000000
6593 3990.00000000
6595 3790.00000000
8899 3290.00000000
18033 0.00000000
18034 0.00000000