What is the best performance search query/method for huge number of records
I need to know what's the best way to search tables with million records. I have a table called <people> and this table has the column: <status>
Status might be more than one word like:
"I am happy today and the weather is nice".
Also, it might be in different languages.
Records in table are represented by UTF-8.
I need to search for a sub-word, word, or even some words out of the whole sentence like for example (according the status above):
Search 1: keyword = "ppy"
Search 2: keyword = "am weather"
Search 3: keyword = "nice"
Search 4: keyword = "day weath"
I would greatly appreciate if you hint me to the best method to apply a robust search. As far as I know using "LIKE" is not practical for huge records. I heard about the full text index but never used that.
Thank you very much!