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 (
    26364, 26365, 26366, 26367, 26368, 26370, 
    26371, 26372, 26373, 26374, 26375, 
    26376, 26377, 26761, 26762, 26763, 
    26764, 26765, 26770, 26796, 26798, 
    26799, 26800, 26967
  ) 
  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.00067

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "16.81"
    },
    "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",
              "idx_product_prices_usergroup_limit"
            ],
            "key": "product_id",
            "used_key_parts": [
              "product_id"
            ],
            "key_length": "3",
            "rows_examined_per_scan": 24,
            "rows_produced_per_join": 4,
            "filtered": "20.00",
            "index_condition": "(`cscart_db`.`cscart_product_prices`.`product_id` in (26364,26365,26366,26367,26368,26370,26371,26372,26373,26374,26375,26376,26377,26761,26762,26763,26764,26765,26770,26796,26798,26799,26800,26967))",
            "cost_info": {
              "read_cost": "16.33",
              "eval_cost": "0.48",
              "prefix_cost": "16.81",
              "data_read_per_join": "115"
            },
            "used_columns": [
              "product_id",
              "price",
              "percentage_discount",
              "lower_limit",
              "usergroup_id"
            ],
            "attached_condition": "((`cscart_db`.`cscart_product_prices`.`lower_limit` = 1) and (`cscart_db`.`cscart_product_prices`.`usergroup_id` in (0,1)))"
          }
        }
      ]
    }
  }
}

Result

product_id price
26364 744.000000
26365 896.800000
26366 993.600000
26367 1000.000000
26368 782.400000
26370 0.000000
26371 0.000000
26372 0.000000
26373 0.000000
26374 360.000000
26375 0.000000
26376 0.000000
26377 0.000000
26761 0.000000
26762 0.000000
26763 0.000000
26764 0.000000
26765 0.000000
26770 0.000000
26796 0.000000
26798 0.000000
26799 0.000000
26800 0.000000
26967 0.000000