Replacement relevance sorting for MySQL fulltext in InnoDB tables?

Question

I am using MySQL InnoDB and want to do fulltext style searches on certain columns. What is the best way of sorting the results in order of relevance?

I am trying to do something like:

SELECT columns,   
(LENGTH(column_name) - LENGTH(REPLACE(column_name, '%searchterm%', ''))) AS score
FROM table
WHERE column_name LIKE '%searchterm%'
ORDER BY score

However this will become quite complicated once I start searching more than 1 column or using more than one keyword. I already have several joins happening so have simplified the query above.

Can anyone suggest a better way? I don't want to use any 3rd party software like Sphinx etc

Answer 1

I would take look at Sphinx or Solr. Both have better fts than MyISAM and can also work for you innoDB-tables.

Answer 2

If you don't want to use another engine, you could create a "shadow" MyISAM copy of your InnoDB table and run the fulltext searches against that. With some appropriate triggers on the InnoDB table, you could update the MyISAM copy relatively easy. And to guard against the chance that the InnoDB-side record has vanished, do a join back against the InnoDB table. Something like:

SELECT MATCH(myisamtable.field1, myisamtable.field2, myisamtable.field3)
        AGAINST ('search terms') AS relevance,
    mysitamtable.field1, myisamtable.field2, myisamtable.field3
FROM myistamtable
RIGHT JOIN innodbtable ON myistamtable.commonID = innodbtable.commonID
WHERE (innodbtable.commonID IS NOT NULL) AND (MATCH(myisamtable.field1, myisamtable.field2, myisamtable.field3) AGAINST ('search terms') IN BOOLEAN MODE)
ORDER BY relevance DESC;