Determining most matches in multiple search

Databases
1

I am wondering, if a search term was “hello there old friend” and each word was a searchword. Is there a way to find out which result contains the most of these words in one field. The following SQL as an example


SELECT 
DISTINCT `ContactID` 
FROM 
`contact` 

WHERE 
      `Notes` LIKE '%hello%'  ||
      `Notes` LIKE '%there%'  ||
      `Notes` LIKE '%old%'  ||
      `Notes` LIKE '%friend%'  
				 
order by 
case when `Notes` like '%hello there my old friend%' then 1 else 0 end
+ case when `Notes` like '%hello%' then 0 else 1 end
+ case when `Notes` like '%there%' then 0 else 1 end
+ case when `Notes` like '%my%' then 0 else 1 end
+ case when `Notes` like '%old%' then 0 else 1 end
+ case when `Notes` like '%friend%' then 0 else 1 end

? If the case when words would it be recommended to break apart the search term so that all permutations and combinations can be found such as
case when Notes like hello there my old
“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”" there my old friend
“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”" hello there my
“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”" there my old
“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”" my old friend
“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”" hello there
“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”" there my
“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”" my old
“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”" old friend
“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”" hello
“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”" there
“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”" my
“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”" old
“”“”“”“”“”“”“”“”“”“”“”“”“”“”“”" friend

Would such a technique be strenous on the mysql query, since a user could technically enter as many words as they would like; it would take longer I suppose to search a phrase of 10 words as oppose to 5 words. Which I guess would add 55 or 15 more lines of code respectively if my memory on statistics is correct

Is there something better?

2

this technique would not be noticeably slower than just searching for the phrase, so go ahead and do it

the performace is marred by even one LIKE with a leading wildcard, which requires a table scan

once you’re doing a table scan anyway, you might was well rip apart each row and analyze it however you want

:slight_smile:

p.s. please use the standard sql OR keyword, instead of those nasty proprietary mysql double pipes

3

I always love it when you respond rudy ;D I’ve replaced the pipes, and ripping the table apart like crazy now haha :smiley: