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 (
    19722, 19583, 19824, 19791, 19585, 19587, 
    19825, 19752, 19588, 19753, 19765, 
    19755, 19754, 19764, 19586, 19593, 
    19594, 19624, 19595, 19626, 19627, 
    19625
  ) 
  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.00128

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": 22,
      "filtered": 77.273,
      "index_condition": "cscart_product_prices.product_id in (19722,19583,19824,19791,19585,19587,19825,19752,19588,19753,19765,19755,19754,19764,19586,19593,19594,19624,19595,19626,19627,19625)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
19583 890.00000000
19585 890.00000000
19586 890.00000000
19587 890.00000000
19588 890.00000000
19593 790.00000000
19594 790.00000000
19595 790.00000000
19624 1290.00000000
19625 1290.00000000
19626 1290.00000000
19627 1290.00000000
19722 790.00000000
19752 890.00000000
19753 890.00000000
19754 790.00000000
19755 790.00000000
19764 790.00000000
19765 990.00000000
19791 890.00000000
19824 990.00000000
19825 990.00000000