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 (
    19175, 19335, 18844, 19299, 19291, 8939, 
    7442, 7441, 7863, 18696, 7721, 7852, 
    7722, 7723, 7853, 7724, 7725, 7854, 
    7855, 7632, 7633, 7634, 7726, 7727
  ) 
  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.00095

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 (19175,19335,18844,19299,19291,8939,7442,7441,7863,18696,7721,7852,7722,7723,7853,7724,7725,7854,7855,7632,7633,7634,7726,7727)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
7441 3990.00000000
7442 4590.00000000
7632 15990.00000000
7633 23990.00000000
7634 17990.00000000
7721 5590.00000000
7722 6990.00000000
7723 15990.00000000
7724 12990.00000000
7725 19990.00000000
7726 23990.00000000
7727 23990.00000000
7852 5990.00000000
7853 9990.00000000
7854 27990.00000000
7855 21990.00000000
7863 4590.00000000
8939 3990.00000000
18696 4290.00000000
18844 2490.00000000
19175 2990.00000000
19291 2190.00000000
19299 2590.00000000
19335 7990.00000000