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 (
    8772, 7307, 6831, 6881, 7306, 7230, 7321, 
    8611, 7878, 12093, 7775, 7231, 8051, 
    8429, 19430, 19432, 19424, 19431, 8612, 
    16492, 16493, 10045, 10049, 16494
  ) 
  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.00095

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 (8772,7307,6831,6881,7306,7230,7321,8611,7878,12093,7775,7231,8051,8429,19430,19432,19424,19431,8612,16492,16493,10045,10049,16494)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
6831 990.00000000
6881 590.00000000
7230 3590.00000000
7231 4290.00000000
7306 3990.00000000
7307 3990.00000000
7321 3990.00000000
7775 5990.00000000
7878 1890.00000000
8051 3790.00000000
8429 2590.00000000
8611 190.00000000
8612 590.00000000
8772 2290.00000000
10045 590.00000000
10049 690.00000000
12093 2190.00000000
16492 590.00000000
16493 590.00000000
16494 190.00000000
19424 490.00000000
19430 390.00000000
19431 790.00000000
19432 790.00000000