How to search a word in MySQL table? MATCH AGAINST or LIKE?

Question

I have a table for some companies that may have many branches in different countries. These countries are inserted in countries field.
Now, I have to make a searching system that allows users to find companies that have any branch in a specific country.
My question is: Which one do I have to use ? MATCH AGAINST or LIKE ? The query must search all records to find complete matched items.

• attention: Records may have different country name separated with a comma.

Answer 1

MATCH AGAINST clause is used in Full Text Search.

for this you need to create a full text index on search column countries.

full text index search is much faster than LIKE '%country%' serach.

Answer 2

I would change the implementation: having a field that contains multiple values is a bad idea, for example, it's difficult to maintain - how will you implement remove a country from a company ?.

I believe that a better approach would be to have a separate table companies_countries which will have two columns: company_id and country_id, and could have multiple lines per company.

Answer 3

You should use LIKE . Because as @Omesh mentioned MATCH AGAINST clause is used for Full Text Search.. And Full Text Search need entire column for search.