To enable fulltext search on a table inMySql, the following syntax is used:
ALTER TABLE your_table_name ADD FULLTEXT (Column_1, Column_2)
Fulltext on MySql will only work on MyISAM type tables.
You can issue a fulltext query as below:
SELECT * FROM your_table_name WHERE MATCH(Column_1, Column_2) AGAINST(‘your-search-query’)
However for a small table with limited number of rows, you may not see the fulltext search results because of the following rule for fulltext:
“…words that are present in 50% or more of the rows are considered common and do not match”
To overcome this problem you can rewrite your query as follows:
You can issue a fulltext query as below:
SELECT * FROM your_table_name WHERE MATCH(Column_1, Column_2) AGAINST(‘your-search-query’ IN BOOLEAN MODE)
This will give you the results you are expecting. You can also try adding more rows to your table. If you have just 2 rows, adding a 3rd one might solve the problem because the 50% rule will be avoided.
To delete fulltext index:
ALTER TABLE your_table_name DROP INDEX your_index_name
To see the index on a table (including fulltext index):
SHOW INDEXES FROM your_table_name