How to search for a text? (MySQL)

Question

I have this table:

bussId    |      nameEn        |          keywords
  500          name1 name2             keyword1 keyword2

I want to return bussId 5000 if the user search for (keyword1 or keyword2 or name2 or name1). So I should use this query SELECT * FROM business WHERE nameEn LIKE '%searched_word%'. But this query doesn't use the index nameEn or keywords, according to Comparison of B-Tree and Hash Indexes "The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character".

I have this solution, I want to create another table and insert all the single words:

bussId |  word
  500        name1
  500        name2
  500        keyword1
  500        keyword2

Then I will search for the bussId using this query:
SELECT * WHERE word LIKE 'searched_word%'.

In that way I will be sure that the MySQL will use the index , and it will be faster, but this table will contain about 20 million rows!

Is there another solution?

Answer 1

You have to use a fulltext index using MyISAM or InnoDB from MySQL 5.6 onwards:

mysql> ALTER TABLE business ADD FULLTEXT(nameEn, keywords);

And here is your request:

mysql> SELECT * FROM business
   -> WHERE MATCH (nameEn, keywords) AGAINST ('searched_word');