匹配查询全文索引不返回mysql中的完整结果

Question

I am having a query in my project where I have to search for records with like query with wildcard search.

Following is the query:

Select * from table_name where Col_name like '% text %';

Now in above query normal indexing wouldn't work. So, I have used full text indexing here in MyISAM mysql table.

After which my query modifies like as follows

Select * from table_name where MATCH(Col_name) AGAINST('text' IN BOOLEAN MODE);

First query gives me 150000 records where as second query gives me only 3000 records.

Please guide me how to get all records which I am getting in first query as well in second query as well.

Answer 1

Full Text Search indexes are based on individual words, that means the phrase row with texts or textWithOtherCharactersAround will no be returned in query result.

The like operator finds in the entire field value according to wildcards.