CODEWITHSUNDEEP
mysqlregexsql-like
Momen Zalabany
Momen Zalabany

Reputation: 9007

Mysql searching multiple words using wildcard like and ordering by count of words matched

I have an innodb table 'not supporting fulltext' that i search using like statments

Select text,id from searchTable where text like '%sub1%' or text like '%sub2%' or text like '%sub3%'
group by text,
order by (CASE
            when text LIKE 'sub1 %' then 1
            when text LIKE 'sub1%'  then 2
            when text LIKE '% sub1%'  then 3
            when text LIKE '%sub1%'  then 4
            else 5
            end),id

this returns results more or less as expected, yet i was wondering if i can also order it by the count of substrings that matched. for example order rows that has all 3 substrings first, followed by rows that matchs 2 out of 3 substrings, etc..

is it possible ? would it impact performance that much ?

my table contains around 50k rows, this current statement takes around 0.6ms to execute.

thanks

Upvotes: 0

Views: 253

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can order by the number of matching substrings by doing:

order by ((test like '%sub1%') +
          (text like '%sub2%') +
          (text like '%sub3%')) desc

In an integer context, MySQL treats boolean values as integers, with 1 for true and 0 for false. So this counts the number of matches.

Upvotes: 2

Related Questions