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 (
    19639, 19640, 19641, 19642, 19663, 19579, 
    19619, 19643, 19771, 19772, 19644, 
    19632, 19580, 19645, 19633, 19646, 
    19685, 19687, 19688, 19689
  ) 
  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.00091

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": 21,
      "filtered": 76.19,
      "index_condition": "cscart_product_prices.product_id in (19639,19640,19641,19642,19663,19579,19619,19643,19771,19772,19644,19632,19580,19645,19633,19646,19685,19687,19688,19689)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
19579 490.00000000
19580 490.00000000
19619 490.00000000
19632 490.00000000
19633 490.00000000
19639 490.00000000
19640 490.00000000
19641 490.00000000
19642 490.00000000
19643 490.00000000
19644 490.00000000
19645 490.00000000
19646 490.00000000
19663 490.00000000
19685 290.00000000
19687 290.00000000
19688 290.00000000
19689 290.00000000
19771 490.00000000
19772 490.00000000