Search code examples
phpmysqlfull-text-searchsql-like

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

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.

Solution

  • 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 :

    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.