In my web app I need to perform 3 types of searching on items
table with the following conditions:
items.is_public = 1
(usetitle
field for indexing) - a lot of results can be retrieved(cardinality is much higher than in other cases)items.category_id = {X}
(usetitle
+private_notes
fields for indexing) - usually less than 100 resultsitems.user_id = {X}
(usetitle
+private_notes
fields for indexing) - usually less than 100 results
I can't find a way to make Sphinx work in all these cases, but it works well in 1st case. Should I use Sphinx just for the 1st case and use plain old "slow" FULLTEXT searching in MySQL(at least because of lower cardinality in 2-3 cases)?
Or is it just me and Sphinx can do pretty much everything?