Filtering trough big arrays, collections vs looping through mysql performance choice

Hi all,

I'm creating a filter tool to filter e-commerce products down.. Meaning the user can choose multiple filters and manipulate, include or exclude data.

So for now i have a rules table, and of course a products table. The rules can have maybe 10, maybe 50 rules (if price < 10 exclude) (if price > 100 change attribute shipping to 0) Those kinds of filters.

But when it involves 100.000 or maybe in some cases 500.000 products. What would be the best practices. Somehow you fetch the rules onces. thats no harm. Then you need to loop through these filters product by product.

Its should be a on the fly thing, since the core value should be kept but after the filter it will either store the products in a xml, csv or send over through a api.

Products -> Filter -> Api.

The problem i have is performance of course. Doing it the most basic dumb way would be looping each product through the filters and then perform the send action (either api, or write to file)

But is it smart to select 500.000 at once, make one big object or array. or do this in batches. Whats your opinion / view on this?