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 (
    19958, 19959, 20134, 19906, 20031, 20032, 
    20183, 19939, 20197, 20196, 20198, 
    20193, 20194, 20195, 20199, 20200, 
    19930, 19985, 19931, 19932, 19895, 
    19933, 20146, 20143
  ) 
  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.00101

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 (19958,19959,20134,19906,20031,20032,20183,19939,20197,20196,20198,20193,20194,20195,20199,20200,19930,19985,19931,19932,19895,19933,20146,20143)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
19895 2990.00000000
19906 2990.00000000
19930 2990.00000000
19931 2990.00000000
19932 2990.00000000
19933 2990.00000000
19939 2990.00000000
19958 4990.00000000
19959 4990.00000000
19985 2990.00000000
20031 2990.00000000
20032 2190.00000000
20134 2990.00000000
20143 2790.00000000
20146 2990.00000000
20183 2990.00000000
20193 2990.00000000
20194 2990.00000000
20195 2990.00000000
20196 2990.00000000
20197 2990.00000000
20198 3490.00000000
20199 2990.00000000
20200 2990.00000000