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 (
    19899, 20209, 20162, 20163, 20164, 20211, 
    20165, 20210, 20160, 20161, 20017, 
    20018, 20020, 20021, 20022, 20019, 
    19936, 19937, 19938, 19926, 19928, 
    19927, 19956, 19957
  ) 
  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.00089

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 (19899,20209,20162,20163,20164,20211,20165,20210,20160,20161,20017,20018,20020,20021,20022,20019,19936,19937,19938,19926,19928,19927,19956,19957)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
19899 2190.00000000
19926 3990.00000000
19927 3990.00000000
19928 3990.00000000
19936 3990.00000000
19937 3990.00000000
19938 3990.00000000
19956 4990.00000000
19957 4990.00000000
20017 1590.00000000
20018 1590.00000000
20019 1590.00000000
20020 1590.00000000
20021 1590.00000000
20022 1590.00000000
20160 1190.00000000
20161 1190.00000000
20162 1590.00000000
20163 1190.00000000
20164 1190.00000000
20165 1590.00000000
20209 1590.00000000
20210 1590.00000000
20211 1590.00000000