Mysql match...against vs. simple like "%term%"

Question

What's wrong with:

$term = $_POST['search'];

function buildQuery($exploded,$count,$query)
{
   if(count($exploded)>$count) 
   {
      $query.= ' AND column LIKE "%'. $exploded[$count] .'%"';
      return buildQuery($exploded,$count+1,$query);
   }
   return $query;
}

$exploded = explode(' ',$term);
$query = buildQuery($exploded,1,
'SELECT * FROM table WHERE column LIKE "%'. $exploded[0] .'%"');

and then query the db to retrieve the results in a certain order, instead of using the myIsam-only sql match...against?

Would it dawdle performance dramatically?

Answer 1

The difference is in the algorithm's that MySQL uses behind the scenes find your data. Fulltext searches also allow you sort based on relevancy. The LIKE search in most conditions is going to do a full table scan, so depending on the amount of data, you could see performance issues with it. The fulltext engine can also have performance issues when dealing with large row sets.

On a different note, one thing I would add to this code is something to escape the exploded values. Perhaps a call to mysql_real_escape_string()