I'm wondering if anyone has done any work extending fulltext search on a SS site. In exploring search contexts, I found that FulltextFilter.php (in sapphire/search/filters) has an out-and-out MySQL syntax error (a missing parenthesis in the MATCH function), so I'm suspecting that this is code that positively no one is exercising or testing. Not only that, but it would inject MATCH into the WHERE clause, which is not what I want.

My particular app would benefit from full-text searches across multiple objects that are related by has_one, and I'm starting to think that I need a denormalized table containing all the text that's to be searchable yielding a fulltext relevancy score. Something like this example:

SELECT ID, SourcePageID, MATCH (Title,Content,ContributedContent,WikiContent) AGAINST ('$search_terms') AS Relevance
FROM MyFullTextTable
HAVING Relevance > 0.5
ORDER BY Relevance DESC

Has anyone barked up this or a similar tree? Would love to hear about it!

### Update 15 May 2009 (California Time)

Seems I had some misconceptions about MATCH usage -- it is intended to be used in the WHERE clause. Here's a nice intro by Joe Stump: http://www.onlamp.com/pub/a/onlamp/2003/06/26/fulltext.html

Glad to help!

In the meantime, we've changed our approach and are integrating sphinxsearch (http://www.sphinxsearch.com/) rather than rely on MySQL's fulltext indexing.