search for multiple values, sort by relevance (php + MySQL, no full text search)

Question

I have a table with title and keywords columns that I'd like to search on. I want to add the ability to use multi-term search, but then it should definitely sort the results by relevance. FullTextSearch is not an option because I'm currently using MySQL5.5 and innoDB.

My current approach is as follows:

1. I search for every term individually and store matching ids in a php array.
2. Once I've searched for all terms I count id occurrences and sort accordingly.

What I'm wondering right now is if there's a more efficient way to do it as I'm primarily using php for this. Should I do some of this processing in the database? Should I change my approach drastically?

The table is relatively small (less than 10k records) and I don't expect it to become much larger in the near future.

Any suggestions? Thanks.

Answer 1

You can do the count in SQL. Here is an example:

select ((col like 'term1') +
        (col like 'term2') +
         . . .
        (col like 'termN')
       ) as NumMatches
from t
having NumMatches > 0
order by NumMatches desc;

MySQL treats booleans as 0 (for false) and 1 (for true). You can add them together to get the total number of matches.