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

Question

MySQL

Hello.
There is a catalog of goods n million.
The search for goods takes place according to several parameters:
Name, price ...
+ filter by price ...
Monga works with a bang, but with a full-text search, to put it mildly, it sucks. Using the sphinx has a significant drawback - you need to pass the product id-s in MongoDB. Complicated by the fact that the found results can be n million + sorting!
I read the doc (not too carefully) like it is possible to link Mysql and Sphinx using SphinxQL.
Has anyone encountered such a problem? How did they decide?

Answer 1

Solution Comparison
1.1)
-- Sphinx search : array ids

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

1.2)
-- Mysql search : array items

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 : array items
In my case, the choice fell on monga. Although it does not have an ideal fulltext search, this method is much more compact and efficient.

Answer 2

SphinxQL is not related to mysql, it is a query language similar to SQL, you can connect to it using any mysql driver.
Sphinx is better integrated with muscle, but it's not that important. In an index it is possible to push the data though whence.
>Complicated by the fact that the found results can be n million + sorting!
Are you going to display millions of products on one page? offset, limit is the same.