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 (
    19799, 19678, 19546, 19811, 19545, 19809, 
    19810, 19815, 19814, 19812, 19813, 
    19558, 19673, 19557, 19850, 19851, 
    19847, 19846, 19845, 19848, 19849
  ) 
  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.00087

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 (19799,19678,19546,19811,19545,19809,19810,19815,19814,19812,19813,19558,19673,19557,19850,19851,19847,19846,19845,19848,19849)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
19545 490.00000000
19546 490.00000000
19557 490.00000000
19558 490.00000000
19673 490.00000000
19678 390.00000000
19799 290.00000000
19809 490.00000000
19810 490.00000000
19811 490.00000000
19812 490.00000000
19813 490.00000000
19814 490.00000000
19815 490.00000000
19845 490.00000000
19846 490.00000000
19847 490.00000000
19848 490.00000000
19849 490.00000000
19850 490.00000000
19851 490.00000000