How to organize a product filter using Sphinx and MongoDB or Mysql?

Question

How to organize a product filter using Sphinx and MongoDB or Mysql?

Hi all.

There is a product catalog n million.
Search products occurs according to several parameters:
Name, price ...
+ filter by price ...

Monga works with a Bang but with a full-text search cope to put it mildly sucks. The use of the Sphinx gives a significant disadvantage - you need to pass id-shnik of the goods in MongoDB. Is complicated by the fact that the results found can be n million + sorting!
Read the Doc ( not too carefully ) it seems to have the ability to link Mysql and Sphinx using SphinxQL.

Encountered someone with this problem? How did you decide on?

Urban66 asked July 9th 19 at 10:22

Related questions

More answers about "How to organize a product filter using Sphinx and MongoDB or Mysql?"

2 answers

Willow17 answered on July 9th 19 at 10:26

SphinxQL is not related to mysql, it is the language saprof pohojii SQL, you can connect to it using any mysql driver.

Sphinx is better integrated with the muscle, but this is not so important. In the index data, at least from stuff.

>Is complicated by the fact that the results found can be n million + sorting!
And what are you going millions of products on one page to display? the offset limit is the same.

Ie it would be wise if I zafigachit a table from my database in Sphinx?
About limi and offset. For example, we found a 100k goods must be sorted by the price. And in order to do this I need to know the id-shnik of goods. 100k id schnick Suva in the query is not the best idea. Maybe I something not so understood... - Urban66 commented on July 9th 19 at 10:29

And if I sort another and the characteristics of the product? - Urban66 commented on July 9th 19 at 10:32

It should be the price to stick to the Sphinx and sort by price-level of the Sphinx. - Willow17 commented on July 9th 19 at 10:35

: you can shove into the index whatever you want. And sort can want. - Willow17 commented on July 9th 19 at 10:38

Urban66 answered on · Accepted Answer · 2019-07-09T10:24:05.644Z

The comparison of solutions
1.1)

-- Sphinx search : array of ids

SELECT *
FROM itemsindex
WHERE name MATCH(@name request)
ORDER BY price DESC

1.2)

-- Mysql search : items array

SELECT *
FROM items
WHERE id IN(ids)
 && EXISTS( SELECT *
 FROM characteristics
 WHERE id = items.id
 && char_val = filterVal )
 && EXISTS( SELECT *
 FROM characteristics
 WHERE id = items.id
 && char_val = filterVal2 )

2)

-- MongoDB search : items array

db.items.find({$text: {$search: request}, characteristics: {char_val: filterVal}});

In my case the choice fell on Mongu. Let it not be perfect the fulltext search, but this method is much more compact and more efficient.