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 (
    21604, 21607, 21606, 21602, 21599, 21617, 
    21616, 21611, 21595, 21597, 21578, 
    21596, 21577, 21561, 21562, 21563, 
    21610, 21615, 21601, 21598, 21591, 
    21592, 21593, 21594
  ) 
  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.00151

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 (21604,21607,21606,21602,21599,21617,21616,21611,21595,21597,21578,21596,21577,21561,21562,21563,21610,21615,21601,21598,21591,21592,21593,21594)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
21561 990.00000000
21562 990.00000000
21563 990.00000000
21577 790.00000000
21578 790.00000000
21591 1290.00000000
21592 1290.00000000
21593 990.00000000
21594 990.00000000
21595 790.00000000
21596 790.00000000
21597 590.00000000
21598 990.00000000
21599 250.00000000
21601 790.00000000
21602 250.00000000
21604 390.00000000
21606 250.00000000
21607 250.00000000
21610 990.00000000
21611 250.00000000
21615 790.00000000
21616 250.00000000
21617 250.00000000