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 (
    21699, 21694, 21695, 21697, 21696, 21698, 
    21691, 21692, 21690, 21693, 21687, 
    21688, 21715, 21683, 21685, 21724, 
    21686, 21684, 21689, 21717, 21716, 
    21719, 21718, 21721
  ) 
  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.00085

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": 26,
      "filtered": 76.923,
      "index_condition": "cscart_product_prices.product_id in (21699,21694,21695,21697,21696,21698,21691,21692,21690,21693,21687,21688,21715,21683,21685,21724,21686,21684,21689,21717,21716,21719,21718,21721)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
21683 590.00000000
21684 590.00000000
21685 590.00000000
21686 590.00000000
21687 590.00000000
21688 590.00000000
21689 590.00000000
21690 590.00000000
21691 590.00000000
21692 590.00000000
21693 590.00000000
21694 590.00000000
21695 590.00000000
21696 590.00000000
21697 590.00000000
21698 590.00000000
21699 590.00000000
21715 590.00000000
21716 590.00000000
21717 590.00000000
21718 590.00000000
21719 590.00000000
21721 590.00000000
21724 590.00000000