How to bypass multiple JOINs in EAV ?

Question

PHP

The task is to develop a product search filter by Yandex.Market type.
As an example, this is a search filter for air conditioners: market.yandex.ua/guru.xml?CMD=-RR%3D5%2C0%2C0%2C0-...
In my previous question, I learned about the EAV structure, read it, understood all the cons and pros. I also saw a lot of recommendations towards mongodb and sphinx. I want to try to combine this case in such a way as to get rid of a lot of joins and linking tables in mysql.
If we follow EAV, we have the following tables: Products, Categories, Attributes, Attribute Value, Product Category Relationship, Product Attributes Relationship, Attribute Value Relationship.
As a result, if for example we are in the Air Conditioners sectionand you need to show all air conditioners whose price is from 1000 to 5000, manufactured by Samsung and with a Motion Sensor.
So the request would be something like this:

SELECT * FROM Товары
JOIN Категории WHERE Кондиционеры
JOIN  Связь Товар Атрибуты
JOIN Связь Атрибут Значение WHERE цена AND самсунг AND Сенсором Движения

Perhaps there will be even more joins. Everything would be fine, but on a large base it will be sad. And the goods will be filled quickly, there will be a lot of them.
I would like to remove the link tables and leave only the following:
- Products
- Categories
- Attributes
- Values
​​Now the actual question that I am confused about, how can I use mongodb and sphinx to search for products?
From the articles I read, I realized that in mongodb you need to store relationships and make a selection specifically through mongodb.
But I didn’t understand what kind of connections should be stored there and how to write them down.
Also, I can’t understand why sphinx is needed. That is, everywhere they describe that this is a cool search engine, I always got the impression that this is a cool analogue of LIKE. Is it proper for the JOIN notation?

Answer 1

A good description of the option with Mongodb
habrahabr.ru/post/259219/#73

Answer 2

You have relational data, you need to use a relational database for this (which is MySQL, PostgreSQL and many others), but not MongoDB.
If you are not confused by 3-5 JOIN (shouldn't be) - then use it. I had about 8-10 JOINs in one project, with proper querying and caching, I consider a speed of several milliseconds (about 10 on average, depending on the request) to be acceptable.

Answer 3

1. Monga just solves the problem of the link table. Actually, that's why they recommend it to you, because it solves the very problem that you just described - it eliminates ties. In it, you can stupidly write "color: red, heating: yes, minimum cooling temperature: 16" - this is what the request looks like, in the form of JSON. But there may be problems with indexes (there are thousands of indexed fields, you can’t get enough indexes), and you won’t find much by searching.
2. And then the piggies come to the rescue.
The dumbest option is to generate text. Sphinx is a full-text search, isn't it? Well, here's the text generated, the same as I wrote above, which lists all the parameters. Looking great. For more advanced options google sphinx faceted search