The implementation of faceted (facet) search on mysql

0 like 0 dislike
7 views
Hello, I want to implement facet search on mysql. In fact it is a column with filters which is very often used to filter the products in the online store. For example in Magento
The data itself is stored in the form of "the product ID" "ID" attribute the "ID filter"

To implement this thing, a number of questions.

1) how to count the number of the filter? In the me example, the link indicates the quantity of items with the specified filters.

2)How best to store it in the table themselves filters if more than one attribute? Separated by commas or each filter in a separate row?

3)Will there be a significant increase when using MEMORY tables if you expect 300K records?

p.s. To make it clearer the attribute is a filter group, the filter is the value by which the filtered

p.s.s I'd love to do it on php mysql without using third-party frameworks.
by | 7 views

2 Answers

0 like 0 dislike
This, as I understand it, is about EAV.
Theoretically, in case of use of MEMORY tables, the increase should be, because in the EAV reading of each field causes a positioning of the head of the hard drive, so reading from memory should be faster.
In General, RDBMSs are not well suited to implement the facet search. Usually brake this search correct search engines, such as Sphinx, but it is better to look aside nosql — mongodb, couchdb, etc.
by
0 like 0 dislike
1) like Magento uses a complex abstract model, which is an interesting way communicates with the database. Changes directly in the database like death — everything should be done via magento api. But for the General case — all ID integer, hang them on the composite indices, all will be considered quickly.
\r
2) in a separate row.
\r
3) 300 K rather engine itself takesabout. The same MEMORY after the fact.
by

Related questions

0 like 0 dislike
1 answer
0 like 0 dislike
3 answers
asked May 22, 2019 by FitTech
0 like 0 dislike
2 answers
asked Jun 10, 2019 by driverx18
0 like 0 dislike
1 answer
asked Apr 14, 2019 by ZetRider
110,608 questions
257,186 answers
0 comments
28,667 users