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 (
    8819, 8453, 8632, 7831, 7167, 8027, 7694, 
    12087, 12088, 12089, 12090, 12084, 
    12085, 12086, 18675, 18680, 18610, 
    18611, 18612, 18633, 18553, 18491, 
    18492, 18493
  ) 
  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.00079

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 (8819,8453,8632,7831,7167,8027,7694,12087,12088,12089,12090,12084,12085,12086,18675,18680,18610,18611,18612,18633,18553,18491,18492,18493)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
7167 64990.00000000
7694 22990.00000000
7831 59990.00000000
8027 20990.00000000
8453 16990.00000000
8632 59990.00000000
8819 11990.00000000
12084 4990.00000000
12085 4990.00000000
12086 4990.00000000
12087 6990.00000000
12088 6990.00000000
12089 6990.00000000
12090 6990.00000000
18491 1990.00000000
18492 1990.00000000
18493 1990.00000000
18553 2490.00000000
18610 990.00000000
18611 990.00000000
18612 690.00000000
18633 2490.00000000
18675 3590.00000000
18680 5990.00000000