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 (
    21088, 20980, 21071, 21074, 21072, 21073, 
    21076, 21119, 21166, 21116, 21117, 
    21118, 21087, 21120, 21096, 21113, 
    21097, 21114, 21105, 21108, 21106, 
    21098, 21110, 21111
  ) 
  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.00116

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 (21088,20980,21071,21074,21072,21073,21076,21119,21166,21116,21117,21118,21087,21120,21096,21113,21097,21114,21105,21108,21106,21098,21110,21111)",
      "attached_condition": "cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,1)"
    }
  }
}

Result

product_id price
20980 590.00000000
21071 590.00000000
21072 590.00000000
21073 590.00000000
21074 590.00000000
21076 590.00000000
21087 1490.00000000
21088 590.00000000
21096 290.00000000
21097 290.00000000
21098 490.00000000
21105 390.00000000
21106 390.00000000
21108 290.00000000
21110 390.00000000
21111 390.00000000
21113 390.00000000
21114 590.00000000
21116 690.00000000
21117 690.00000000
21118 690.00000000
21119 590.00000000
21120 390.00000000
21166 590.00000000