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 (
    276693, 276694, 276695, 276696, 276697, 
    276698, 276699, 276700, 276701, 276702, 
    276703, 276704, 276705, 276706, 276707
  ) 
  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.00063

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "10.51"
    },
    "grouping_operation": {
      "using_filesort": false,
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_master_products_storefront_min_price",
            "access_type": "system",
            "possible_keys": [
              "PRIMARY"
            ],
            "rows_examined_per_scan": 0,
            "rows_produced_per_join": 1,
            "filtered": "0.00",
            "const_row_not_found": true,
            "cost_info": {
              "read_cost": "0.00",
              "eval_cost": "0.10",
              "prefix_cost": "0.00",
              "data_read_per_join": "16"
            },
            "used_columns": [
              "storefront_id",
              "product_id",
              "price"
            ]
          }
        },
        {
          "table": {
            "table_name": "cscart_product_prices",
            "access_type": "range",
            "possible_keys": [
              "usergroup",
              "product_id",
              "lower_limit",
              "usergroup_id"
            ],
            "key": "product_id",
            "used_key_parts": [
              "product_id"
            ],
            "key_length": "3",
            "rows_examined_per_scan": 15,
            "rows_produced_per_join": 2,
            "filtered": "20.00",
            "index_condition": "(`s2cart`.`cscart_product_prices`.`product_id` in (276693,276694,276695,276696,276697,276698,276699,276700,276701,276702,276703,276704,276705,276706,276707))",
            "cost_info": {
              "read_cost": "10.21",
              "eval_cost": "0.30",
              "prefix_cost": "10.51",
              "data_read_per_join": "71"
            },
            "used_columns": [
              "product_id",
              "price",
              "percentage_discount",
              "lower_limit",
              "usergroup_id"
            ],
            "attached_condition": "((`s2cart`.`cscart_product_prices`.`lower_limit` = 1) and (`s2cart`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
          }
        }
      ]
    }
  }
}

Result

product_id price
276693 0.00000000
276694 0.00000000
276695 0.00000000
276696 0.00000000
276697 0.00000000
276698 0.00000000
276699 0.00000000
276700 0.00000000
276701 0.00000000
276702 0.00000000
276703 0.00000000
276704 0.00000000
276705 0.00000000
276706 0.00000000
276707 0.00000000