How to search for obtaining the "best" result

Question

Hi guys, suppose someone enter this search (on an form):

Nicole Kidman films

Which SQL i can use to find "the best" results ?

I suppose something like this :

SELECT * FROM myTable WHERE ( Field='%Nicole Kidman Films%' OR Field='%Nicole%' OR Field='%Kidman%' OR Field='%Films%' ) 

My question is how to get most relevant result ? Thank you very much!

Answer 1

What you're looking for is often called a "full text search" or a "natural language search". Unfortunately it's not standard SQL. Here's a tutorial on how to do it in mysql: http://devzone.zend.com/article/1304

You should be able to find examples for other database engines.

Answer 2

In SQL, the equals sign doesn't support wildcards in it - your query should really be:

SELECT * 
  FROM myTable 
 WHERE Field LIKE '%Nicole Kidman Films%' 
    OR Field LIKE '%Nicole%' 
    OR Field LIKE '%Kidman%' 
    OR Field LIKE '%Films%'

But wildcarding the left side won't use an index, if one exists.

A better approach is to use Full Text Searching, which most databases provide natively but there are 3rd party vendors like Sphinx. Each has it's own algorithm to assign a rank/score based on the criteria searched on in order to display what the algorithm deems most relevant.

Answer 3

Full-Text Search:

SELECT * FROM myTable WHERE MATCH(Field) AGAINST('Nicole Kidman Films')

This query will return rows in order of relevancy, as defined by the full-text search algorithm.

Note: This is for MySQL, other DBMS have similar functionality.