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 (
    4487, 4731, 4754, 4772, 4756, 4476, 4477, 
    4554, 4556, 4558, 4621, 4773, 4703, 
    4623, 4624, 18036, 4757, 4702, 4560, 
    4478, 4774, 4494, 4493, 18037
  ) 
  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.00110

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 (4487,4731,4754,4772,4756,4476,4477,4554,4556,4558,4621,4773,4703,4623,4624,18036,4757,4702,4560,4478,4774,4494,4493,18037)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
4476 9990.00000000
4477 11490.00000000
4478 7490.00000000
4487 16990.00000000
4493 7990.00000000
4494 7790.00000000
4554 11990.00000000
4556 11990.00000000
4558 13490.00000000
4560 10990.00000000
4621 19990.00000000
4623 20990.00000000
4624 20990.00000000
4702 11490.00000000
4703 16990.00000000
4731 19990.00000000
4754 19990.00000000
4756 18590.00000000
4757 11490.00000000
4772 21990.00000000
4773 16990.00000000
4774 7490.00000000
18036 9990.00000000
18037 7490.00000000