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 (
    19390, 19391, 16954, 8125, 8126, 7969, 
    8357, 8299, 8879, 8358, 7451, 8671, 
    8242, 8124, 8239, 8376, 8814, 8377, 
    8241, 12403, 8238, 8159, 8240, 8375
  ) 
  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.00092

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 (19390,19391,16954,8125,8126,7969,8357,8299,8879,8358,7451,8671,8242,8124,8239,8376,8814,8377,8241,12403,8238,8159,8240,8375)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
7451 2590.00000000
7969 3990.00000000
8124 2590.00000000
8125 2990.00000000
8126 3990.00000000
8159 1290.00000000
8238 1290.00000000
8239 590.00000000
8240 490.00000000
8241 390.00000000
8242 2590.00000000
8299 4990.00000000
8357 2990.00000000
8358 1990.00000000
8375 490.00000000
8376 590.00000000
8377 790.00000000
8671 1190.00000000
8814 3590.00000000
8879 1290.00000000
12403 7990.00000000
16954 50.00000000
19390 1290.00000000
19391 1290.00000000