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 (
    8835, 8759, 8065, 8460, 8289, 8551, 7416, 
    8153, 7443, 8657, 7653, 7654, 7839, 
    7840, 7841, 7697, 7789, 7902, 7899, 
    7900, 7901, 7609, 7413, 8984
  ) 
  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.00085

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 (8835,8759,8065,8460,8289,8551,7416,8153,7443,8657,7653,7654,7839,7840,7841,7697,7789,7902,7899,7900,7901,7609,7413,8984)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
7413 3990.00000000
7416 3290.00000000
7443 5990.00000000
7609 3590.00000000
7653 990.00000000
7654 790.00000000
7697 3590.00000000
7789 3990.00000000
7839 1990.00000000
7840 1990.00000000
7841 1990.00000000
7899 1.00000000
7900 1.00000000
7901 1.00000000
7902 1.00000000
8065 1690.00000000
8153 390.00000000
8289 2390.00000000
8460 1290.00000000
8551 2590.00000000
8657 4590.00000000
8759 1290.00000000
8835 1290.00000000
8984 61990.00000000