Some questions related to SphinxSE and RT indexes

Question

I consider using Sphinx search in one of my projects so I have a few questions related to it.

1. When using SphinxSE and RT index, every UPDATE or INSERT in the SphinxSE table will update the index, right? No need to call indexer or anything?
2. Can I search on both tags (user entered keywords for a document) and the content and give more relevance to the tag matches? And if it's possible how do I implement the tag search (now I have them in separate tables like an inverted index)
3. For the fillter attributes is it better to stick duplicates of them in the SphinxSE table or fillter using mysql from the regular documents table I have?

Thanks in advance!

Answer 1

OK, I finally understand how things work with the sphinx thing.

1. You cannot INSERT or UPDATE directly the SphinxSE table. Instead you use INSERT/REPLACE while connected to SphinxQL (directly to sphinx daemon).

2. With 1.10 you can add multiple FullText searchable fields. I added title, tags and content. And the query to give more weight to the title, then tags and then content looks like this: SELECT SQL_NO_CACHE * FROM sphinx_docs WHERE query = 'a lot of keywords;weights=3,2,1;'; I use the SQL_NO_CACHE to tell mysql not to cache the result of this, because on next calls I can't get the number of rows returned from sphinx (SHOW STATUS LIKE 'sphinx_total_found')

3. It's better to let sphinx do all the sorting, filltering and use mysql only to JOIN the table you need more info from.

In addition I have to say that I tried many times to add the sphinxse plugin to mysql without success (endless make waiting hours) so I switched to MariaDB 5.2.4 which includes the SphinxSE storage engine.