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 (
    19407, 19411, 19408, 19370, 19491, 19493, 
    19492, 19494, 19375, 19374, 19377, 
    19378, 19379, 19376, 19380, 19381, 
    19382, 19392, 19393, 19387, 19388, 
    19389, 19390, 19391
  ) 
  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.00093

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 (19407,19411,19408,19370,19491,19493,19492,19494,19375,19374,19377,19378,19379,19376,19380,19381,19382,19392,19393,19387,19388,19389,19390,19391)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
19370 1290.00000000
19374 1290.00000000
19375 1590.00000000
19376 990.00000000
19377 1290.00000000
19378 1590.00000000
19379 1990.00000000
19380 1190.00000000
19381 1590.00000000
19382 1990.00000000
19387 690.00000000
19388 790.00000000
19389 790.00000000
19390 1290.00000000
19391 1290.00000000
19392 1590.00000000
19393 1590.00000000
19407 590.00000000
19408 590.00000000
19411 1990.00000000
19491 790.00000000
19492 1790.00000000
19493 490.00000000
19494 590.00000000