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 (
    5133, 5349, 4928, 5044, 4908, 5099, 5100, 
    5101, 5102, 5344, 5342, 5343, 5103, 
    15724, 15725, 5105, 5106, 5084, 5085, 
    5158, 4349, 5005, 5004, 4863
  ) 
  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.00113

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 (5133,5349,4928,5044,4908,5099,5100,5101,5102,5344,5342,5343,5103,15724,15725,5105,5106,5084,5085,5158,4349,5005,5004,4863)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
4349 890.00000000
4863 1990.00000000
4908 5790.00000000
4928 1890.00000000
5004 1990.00000000
5005 1990.00000000
5044 2290.00000000
5084 2490.00000000
5085 2490.00000000
5099 1590.00000000
5100 1590.00000000
5101 1690.00000000
5102 1990.00000000
5103 1790.00000000
5105 2290.00000000
5106 2290.00000000
5133 990.00000000
5158 890.00000000
5342 790.00000000
5343 790.00000000
5344 790.00000000
5349 1290.00000000
15724 1790.00000000
15725 1790.00000000