user2722667 user2722667 - 5 months ago 29
MySQL Question

MySql can't decide between LIKE and MATCH for text search

I am building an application in Laravel. And I can't decide to go with

Match()
or
Like
for text searching.

I only want to do a text search on one column, that is a
Varchar(42)
.

I will also filter out the query by some
Where()
statements, so it will not do a text search on all rows.

I am using mysql 5.6+ so Match works with my innobd engine.


  1. Does Match() do good in a table that has about 30k rows?

  2. Laravel ORM doesnt support match so my query looks like this:

    $q = Input::get('query');
    Post::whereRaw("MATCH(title) AGAINST(? IN BOOLEAN MODE)", array($q))->get();



Do I need to sanitize the "$q" in order to be safe from SQL injections? Since I'm using
whereRaw()

Answer

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.

Also, 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.