Best way to build a SMART mySQL & PHP search engine?

Question

What is the best way to build a mySQL & PHP search?

I am currently using things like

%term%

I want it to be able to find the result even if they spell it slightly wrong, for example:

Field value = "One: Stop Shop:

They search:

One Shop Stop

OR

One Stop Shop

Etc.. I want a really smart search so they find the information even if they don't search the exact thing.

What is the best way to build a smart search like this?

Answer 1

like '%term%' is terrible slow and unoptimized , you might want to add full-text for this column, and use boolean mode for this

Such as

match(column) against('+One +Shop +Stop' in boolean mode)

Take note on the min word lengths is 4, so, you need to consider change it to three, and full-text search only available for myisam

Other opensource search engine like sphinx is ideal for this too

Answer 2

Ajreal is right...just thought I'd add an example to help out:

$query = sprintf("SELECT *, 
         MATCH(col1, col2) AGAINST('%s' IN BOOLEAN MODE) AS relevance
     FROM my_table
     ORDER BY relevance DESC LIMIT 20", $keyword);
$rs = $conn->query($query);

Hope this helps