full-text search mysql 5.6

Full-text indexes can be used only with InnoDB or MyISAM tables, and can be created only for CHAR, VARCHAR, or TEXT columns.
Full-Text index is an index type of FULLTEXT


Basic syntax of  full-text search function is:
MATCH(col1, col2, col3, ….) AGAINST (search_expr [search_modifier])


There are three different types of full-text searches available in MySQL

Boolean Search
Natural Language Search
Query Expansion Search


mysql> CREATE TABLE honnikery (
      id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
      name VARCHAR(200),
      description TEXT,
      FULLTEXT (name,description)
    ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)






INSERT INTO honnikery (name,description) VALUES
    ('prabhakar ','bidar to bangalore'),
    ('Naveen','used only with InnoDB or MyISAM tables '),
    (' MySQL','searches available in MySQL'),
    (' Tricks','tables AUTO_INCREMENT NOT NULL PRIMARY KEY');


mysql> SELECT * FROM honnikery
    WHERE MATCH (name,description)
    AGAINST ('tables' IN NATURAL LANGUAGE MODE);
+----+-------------------+------------------------------------------+
| id | name             | description                                     |
+----+-------------------+------------------------------------------+
|  2 | Naveen            | used only with InnoDB or MyISAM tables            |
|  4 | Tricks            | tables AUTO_INCREMENT NOT NULL PRIMARY KEY |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)