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 it's 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()

Answer 2

You can check out my recent presentation I did for MySQL University:

http://forge.mysql.com/wiki/Practical%5FFull-Text%5FSearch%5Fin%5FMySQL

Slides are also here:

http://www.slideshare.net/billkarwin/practical-full-text-search-with-my-sql

In my test, using LIKE '%pattern%' was more than 300x slower than using a MySQL FULLTEXT index. My test data was 1.5 million posts from the StackOverflow October data dump.