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.
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