Best way to create searchable and sortable product database
If an index can be defined on the search criteria then this will work OK at any scale. BTree indexes have O(log N) performance IIRC so working through 100,000 rows will be 5/3 the time needed for 1,000 rows.

However .. most systems can't fulfill that criteria. Often a user can fill as many or few search fields as she chooses. Then things get complicated. Sometimes it may be best to define separate queries, and indexes, for each combination of search values. This quickly gets large. 5! = 120; that's a lot of indexes and queries. Some DBMS have optimisations for generic searches - OPTION (RECOMPILE) in SQL Server, for example, will cause parameter embedding. Probably some combination of often-used specific queries and a generic catch-all will work.

As the list gets hugh users will expect ever fuzzier searches. At some stage a full text search on the product name and / or description may be useful. Maybe, eventually, you offload that task entirely.