Replacement relevance sorting for MySQL fulltext in InnoDB tables?
- by Giles Smith
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