Replacement relevance sorting for MySQL fulltext in InnoDB tables?
Posted
by Giles Smith
on Stack Overflow
See other posts from Stack Overflow
or by Giles Smith
Published on 2010-03-30T14:08:20Z
Indexed on
2010/03/30
16:23 UTC
Read the original article
Hit count: 627
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
© Stack Overflow or respective owner
