13

I know how to do a regular php mysql search and display the results. However, because of the nature of what I'm trying to accomplish I need to be able to sort by relevancy. Let me explain this better:

Normal Query "apple iphone applications" will search the database using %apple iphone application%, but if there aren't records which display that phrase in that exact order the search will produce nothing.

What I basically need to do is search for 'apple', 'iphone' and 'applications' all separately and then merge the results into one, and then I need to grade the relevancy by how many instances of the word are found in the records. For example if I did what I wanted to do and it returned them following:

Iphone Applications From Apple
Apple Make The Best Apple Iphone Applications
Iphone Applications

They would rank as follows:

Apple Make The Best Apple Iphone Applications
Iphone Applications From Apple
Iphone Applications

Because of how many instances of the search terms are found. See highlighted:

[Apple] Make The Best [Apple] [Iphone] [Applications]
[Iphone] [Applications] From [Apple]
[Iphone] [Applications]

I hope I have explained this well enough and I would be extremely grateful if anyone could give me any pointers.

zuk1
  • 18,009
  • 21
  • 59
  • 63

5 Answers5

9

take a look at the MySQL FULLTEXT search functions, These should automatically return results by relevancy, and give you much more control over your searches

The only potential issue with using fulltext indexes is that they aren't supported by InnoDB tables.

Neil Aitken
  • 7,856
  • 3
  • 41
  • 40
  • 6
    I know this is old, but if someone stumbles on this. they are supported now by innoDB – Dreaded semicolon Feb 23 '17 at 04:52
  • Thank you for this comment. I spoke with colegue of mine and he assured me that I can not use FULLTEXT, but since I read your comment.... Thank you. – Korovjov Jun 13 '20 at 11:26
8

Maybe this might help someone (order by relevance and keep word count):

None full-text:

SELECT *, ( (value_column LIKE '%rusten%') + (value_column LIKE '%dagen%') + (value_column LIKE '%bezoek%') + (value_column LIKE '%moeten%') ) as count_words
FROM data_table
WHERE (value_column LIKE '%dagen%' OR value_column LIKE '%rusten%' OR value_column LIKE '%bezoek%' OR value_column LIKE '%moeten%')
ORDER BY count_words DESC

Full-text:

SELECT * FROM data_table
WHERE MATCH(value_column) AGAINST('+dagen +rusten +bezoek +moeten' IN BOOLEAN MODE)
ORDER BY MATCH(value_column) AGAINST('+dagen +rusten +bezoek +moeten' IN BOOLEAN MODE) DESC;
Martijn H.
  • 315
  • 4
  • 11
  • 1
    This worked for me. I wanted results ordered by relevance but I also wanted partial matches returned, which full-text does not support. – cmann Jun 09 '15 at 22:14
5

A quick google gave me this link.

Example: 

select title, match (title,content) against (”internet”) as score 
from cont 
where match (title,content) against (”internet”) limit 10;
Filip Ekberg
  • 36,033
  • 20
  • 126
  • 183
1
SELECT field2, field3, ..., MATCH(field1, field2) AGAINST ("search string") AS relevance WHERE MATCH(field1, field2) AGAINST "search string" ORDER BY relevance DESC LIMIT 0,10

In the result set, there will be a field "relevance", which is used here to sort the results.

Murat Ayfer
  • 3,894
  • 6
  • 29
  • 26
  • For anyone that wants to match "search string" as two separate words (i.e. either word is relevant) and you have a FULLTEXT index on your columns, you might need to use AGAINST ('search string' IN BOOLEAN MODE) to ensure that the relevance score returned is not 0. I needed to add this to my queries because MySQL only returned a relevance score if the exact phrase matched, and not when only some of the words matched. Murat's example is good because MySQL does not appear to sort by relevance when AGAINST (... IN BOOLEAN MODE) is used in a WHERE clause. – Noel Whitemore Sep 07 '18 at 16:11
0

I Don't What exactly you want but the following code definitely work for you.

SELECT ("some text here" or `column_name`) RLIKE "Apple|Iphone|Application" AS Result ORDER BY Result DESC;

Separate all words with Bar(|) but results will be 1 or 0 founded or not resp. If you want to get founded rows see below.

SELECT * FROM "table_name" WHERE `column_name` RLIKE "Apple|Iphone|Application";
Rohit Kumar
  • 1
  • 1