How to implement faceted navigation in an online store?

Question

PHP

Hello! I am creating an online store and want to use "smart" filters in the product catalog for a quick search. From the examples I found an implementation on wildberies . It's done in such a way that you select orange, the filters are updated, and other attributes (size, brand) only show values ​​that match the orange color.
I store product attributes in the database with the following schema
product_id | attribute_id | value_id
Questions: what algorithm is suitable for implementing such a filter? How to count the number of products for each attribute value?

Let's take for example a basic set of attributes: Brand, size, color

PS: I have already tried several implementations, but there were difficulties at different stages.

Answer 1

On elastic, this is solved like this https://project-a.github.io/on-site-search-design-...
on mysql - it is also possible, through queries with filtering by selected values ​​in the form and grouping by attributes of all available values for goods. Here, with the growth of data, the response time will sag, but this must be estimated based on the amount of data on the project that is being searched.

Answer 2

RDBMS do not do well with such tasks. They are usually implemented by full-text search engines like Elasticsearch.

Answer 3

Given that the products are in the database, then by accessing the product filter by facet, you can get the desired product_id when accessing the database by attribute_id .
For example, via SELECT `product_id` FROM `database` WHERE `attribute_id` = 939;
in MySQL, you can get both the number of affected rows and the products themselves