Implementation of faceted (faceted) search in mysql

Question

PHP

Good afternoon, I want to implement faceted search in mysql. In fact, this is a column with filters that is very often used to filter products in an online store. For example , in Magento, the
data itself is stored in the form of "product id" "attribute id" "filter id"

And a number of questions arose regarding the implementation of this thing.

1) how to quickly calculate the amount of the filter? In the example I specified, by reference, the number of products with the specified filters is indicated.

2) How is it better to store the filters themselves in the table if there are several of them for one attribute? Separate by commas or each filter in a separate row?

3) Will there be a significant gain when using the MEMORY table type if 300k records are expected?

ps To make it clearer, the attribute is a group of filters, the filter is the values ​​by which we filter

pss I would very much like to do this in php mysql without using third-party frameworks.

Answer 1

This, as I understand it, is about EAV .
Theoretically, in the case of using MEMORY tables, the increase should be, because in EAV, reading each field causes the positioning of the hard drive head, so reading from memory should be faster.
In general, RDBMSs are not well suited to implement faceted search. Usually the brakes of such a search are fixed by search engines, such as the sphinx, but it's better to look in the direction of nosql - mongodb, couchdb, etc.

Answer 2

1) how-how... Magento uses a complex abstract model that communicates with the database in an interesting way. Changes directly to the database are similar to death - everything must be done through the magento api. And for the general case - all IDs are integer, hang composite indices on them, everything will be calculated quickly.
2) in a separate row.
3) 300 k rather the engine itself will cache. The same MEMORY in fact.