I am building an application in Laravel. And I can't decide to go with
$q = Input::get('query');
Post::whereRaw("MATCH(title) AGAINST(? IN BOOLEAN MODE)", array($q))->get();
The two capabilities are quite different, so the choice should be easy.
MATCH is focused on words within the text. So, if you want to search by one or more words, then
MATCH should be faster. However,
MATCH is focused on words, so searching on numbers, stop words, and short words requires extra effort.
LIKE generally cannot make use of an index. This slows down such queries because every row needs to be processed. Of course, if the rest of the filtering reduces this to 100 rows, then it is not a big deal.
LIKE can use an index for "prefix" searches -- that is, searches at the beginning of the string. So,
LIKE 'abc%' can use an index. `LIKE '%abc%' cannot.