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 (
    19359, 19367, 19532, 19469, 19499, 19521, 
    19500, 19487, 19522, 19446, 19372, 
    19482, 19467, 19481, 19509, 19510, 
    19511, 19479, 19444, 19518, 19498, 
    19436, 19425, 19426
  ) 
  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.00094

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 (19359,19367,19532,19469,19499,19521,19500,19487,19522,19446,19372,19482,19467,19481,19509,19510,19511,19479,19444,19518,19498,19436,19425,19426)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
19359 490.00000000
19367 1990.00000000
19372 490.00000000
19425 390.00000000
19426 290.00000000
19436 290.00000000
19444 490.00000000
19446 5990.00000000
19467 490.00000000
19469 590.00000000
19479 390.00000000
19481 290.00000000
19482 490.00000000
19487 590.00000000
19498 290.00000000
19499 1190.00000000
19500 590.00000000
19509 190.00000000
19510 290.00000000
19511 390.00000000
19518 490.00000000
19521 1290.00000000
19522 590.00000000
19532 590.00000000