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 (
    20482, 20543, 20496, 20495, 20492, 20489, 
    20498, 20497, 20540, 20541, 20542, 
    20491, 20422, 20493, 20480, 20483, 
    20467, 20477, 20511, 20494, 20499, 
    20234, 20272, 20479
  ) 
  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.00072

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 (20482,20543,20496,20495,20492,20489,20498,20497,20540,20541,20542,20491,20422,20493,20480,20483,20467,20477,20511,20494,20499,20234,20272,20479)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
20234 200.00000000
20272 290.00000000
20422 200.00000000
20467 190.00000000
20477 200.00000000
20479 190.00000000
20480 200.00000000
20482 200.00000000
20483 200.00000000
20489 190.00000000
20491 190.00000000
20492 190.00000000
20493 190.00000000
20494 190.00000000
20495 200.00000000
20496 200.00000000
20497 200.00000000
20498 200.00000000
20499 200.00000000
20511 200.00000000
20540 200.00000000
20541 200.00000000
20542 200.00000000
20543 200.00000000