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 (
    8099, 7913, 7914, 7232, 7763, 7907, 7908, 
    7754, 7374, 7407, 7884, 7885, 7323, 
    7322, 7324, 7398, 7626, 8741, 7821, 
    8921, 8252, 8936, 7765, 7766
  ) 
  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.00088

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 (8099,7913,7914,7232,7763,7907,7908,7754,7374,7407,7884,7885,7323,7322,7324,7398,7626,8741,7821,8921,8252,8936,7765,7766)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
7232 1990.00000000
7322 6990.00000000
7323 4990.00000000
7324 6990.00000000
7374 1990.00000000
7398 2590.00000000
7407 2290.00000000
7626 2590.00000000
7754 2590.00000000
7763 2190.00000000
7765 2390.00000000
7766 7990.00000000
7821 1990.00000000
7884 6990.00000000
7885 6990.00000000
7907 4290.00000000
7908 6590.00000000
7913 2190.00000000
7914 1690.00000000
8099 990.00000000
8252 990.00000000
8741 990.00000000
8921 990.00000000
8936 2590.00000000