What is the best performance search query/method for huge number of records

[Moderns]

Hello,

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!

Cheers,

[pritaeas]

You can try using LIKE, but enabling full-text search may be a faster alternative.

As far as I understand it, if you have a table that isn't updated very frequently, building an index on the status field would improve search performance with minimal overhead when running SELECT queries. The index has to be rebuilt each time the table is updated though, so if this is very frequent (like a status could be) it will create a lot of extra work for the database, thus decreasing performance. It's a balance bwtewwn the 2.