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.

I have changed the column "status" to full text index and the engine is MyISAM. I have tried the below sql but it is not working I am getting the error:"test" AND people.id != "'.$this->myId.'" ORDER BY people.time ASC LIMIT '.$limit;

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near "test"

$keySearch="test";$limit=10;$this->myId=1;

$sql = ' SELECT people.status, people.id FROM people WHERE MATCH (people.status) AGAINST "'.$keySearch.'" AND people.id != "'.$this->myId.'" ORDER BY people.time ASC LIMIT '.$limit;

[quote="da manual"]Such a technique works best with large collections (in fact, it was carefully tuned this way). For very small tables, word distribution does not adequately reflect their semantic value, and this model may sometimes produce bizarre results. For example, although the word “MySQL” is present in every row of the articles table shown earlier, a search for the word produces no results: