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 (
    7637, 8883, 8384, 8385, 8896, 8881, 8826, 
    7811, 7809, 7810, 7808, 7807, 8882, 
    7696, 7403, 8847, 8825, 7898
  ) 
  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.00077

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": 18,
      "filtered": 77.778,
      "index_condition": "cscart_product_prices.product_id in (7637,8883,8384,8385,8896,8881,8826,7811,7809,7810,7808,7807,8882,7696,7403,8847,8825,7898)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
7403 2990.00000000
7637 3990.00000000
7696 2990.00000000
7807 4590.00000000
7808 4590.00000000
7809 3790.00000000
7810 3990.00000000
7811 3790.00000000
7898 1.00000000
8384 1990.00000000
8385 990.00000000
8825 1990.00000000
8826 2590.00000000
8847 1990.00000000
8881 1790.00000000
8882 1790.00000000
8883 590.00000000
8896 2590.00000000