How to make a convenient search with a smart filter in an online store?

Question

Sphinx

Good afternoon!

The task, from my point of view, is not so trivial. It consists in doing a search like in large online stores, i.e. search using the filter system showing the number of products when this filter is selected.


For example, this store http://www.zappos.com/mens-clothing~2S immediately shows that when choosing the 2XU brand, 32 products will be displayed (this was calculated in advance). After selection, all parameters will be recalculated again and will reflect the real state of affairs, i.e. in these 32 products there will be 24 black ones.

This raises 2 questions :

1. How to organize data storage

Once I already asked the question Why is EAV a bad solution for an online store? , but has not yet chosen a method, the choice between EAV and a separate table for each type of product (products can be completely different, and the parameters can also be different, for example, TVs, phones, clothes, etc.)

2. How to actually organize such a filter

I immediately see 2 solutions:

• Load all products matching the filter into memory and already there calculate future numbers with a possible choice of filter
• Make 300+ requests to the database, for each parameter, to find out the number of products with this parameter

Of course, there are still many unanswered questions (how to divide goods by type so that extra parameters, for example, for a TV, are not shown when searching for a jacket) and others. If anyone knows a book, or what to read on this topic, I will be grateful. I want to make a really convenient online store, and not produce clumsy catalogs.

Answer 1

I would do this - EAV for information storage, Sphinx for search.
Then these 300+ requests of yours will be made not to the database, but to the sphinx, and if this is implemented as multi-requests to the sphinx, then it will work very quickly.
Well, not all requests can be made in advance, but only the main ones, and as you choose, load it with Ajax

Answer 2

market.yandex.ua/guru.xml?CMD=-RR=9 ,0,0,0-VIS=70-CAT_ID=432460-EXC=1-PG=10&hid=91013
I have never seen better in my life

Answer 3

As you correctly noted, these are called facets.
As a rule, full-text or flexible search by a large number of parameters is implemented on a search engine - Sphinx or Solr (formerly Lucene).
I don’t know how in the first one, but in the second it’s extremely simple, in one request - wiki.apache.org/solr/SimpleFacetParameters

Answer 4

Judging by the speed, there is something like OLAP. That is, everything is preliminarily calculated; when a new product is added, the corresponding summary figures are updated.

Answer 5

I did it with three tables
1. In one table I stored catalog objects
2. For communication, the object id, property id, property value
3. property id, property name,
well, you can make a selection like this for any number of properties and find out the number of goods there ... mysql and only ... with large volumes it will work fine