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 (
    21664, 21663, 21646, 21644, 21647, 21645, 
    21648, 21650, 21651, 21649, 21641, 
    21640, 21642, 21643, 21634, 21636, 
    21635, 21639, 21637, 21638, 21667, 
    21665, 21666
  ) 
  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.00103

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": 23,
      "filtered": 78.261,
      "index_condition": "cscart_product_prices.product_id in (21664,21663,21646,21644,21647,21645,21648,21650,21651,21649,21641,21640,21642,21643,21634,21636,21635,21639,21637,21638,21667,21665,21666)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
21634 2590.00000000
21635 2590.00000000
21636 2590.00000000
21637 2590.00000000
21638 2590.00000000
21639 2590.00000000
21640 2590.00000000
21641 2590.00000000
21642 2590.00000000
21643 2590.00000000
21644 2590.00000000
21645 2590.00000000
21646 2590.00000000
21647 2590.00000000
21648 2590.00000000
21649 2590.00000000
21650 2590.00000000
21651 2590.00000000
21663 2590.00000000
21664 2590.00000000
21665 2590.00000000
21666 2590.00000000
21667 2590.00000000