MySQL.. search using Fulltext or using Like? What is better?

Question

I'm working on a search feature for my application, I want to search all articles in the database. As of now, I'm using a LIKE in my queries, but I want to add a "Related Articles" feature, sort of like what SO has in the sidebar (which I see as a problem if I use Like).

What's better to use for MySQL searching, Fulltext or Like... or anything else I might not know about?

Also, I'm using the Kohana Framework, so If anybody knows an easy way to do fulltext matching using the query builder, I'd appreciate that.

Thanks.

Answer 1

First of all, you have to consider that :

• MySQL FULLTEXT indexes are only possibles for tables in MyISAM -- not for tables in InnoDB
  • And only InnoDB supports foreign keys, transactions, ...
• Using LIKE is generally pretty slow and bad for performances, as it generally means doing a full-scan of the table for each search.

I would really not use any of those two solutions for any kind of big/real/important application...

Instead of those two possibilities, have you considered using a real fulltext indexing/search engine ?

For instance, you might want to take a look at :

• Solr / Lucene
• Sphinx

Note there is an implementation of Lucene that only requires PHP in Zend Framework : Zend_Search_Lucene -- might be useful if you cannot install any additionnal software on your server.