Full text index is a special type of index that can be used to search words data field in a table.

According to MySQL documentation:

Full-text indexes can be used only with MyISAM tables, and can be created only for CHAR, VARCHAR, or TEXT columns.

And here is an excerpt from High Performance MySQL:

A full-text index is a special type of index that can quickly retrieve the locations of every distinct word in a field. MySQL’s provides full-text indexing support in MyISAM tables. Full-text indexes are built against one or more text fields (VARCHAR, TEXT, etc.) in a table.

The full-text index is also stored in a table’s .MYI file. It is implemented by creating a normal two-part MyISAM B-tree index in which the first field is a VARCHAR, and the second is a FLOAT. The first field contains the indexed word, and the FLOAT is its local weight in the row.

Whereas like is a standard SQL term used for matching pattern in the query. In MySQL, you can also match any arbitrary characters using ‘%’ or you can also use regular expression. But use of like when matching a word in a query is quite costly in terms of time than using Full Text Index.

Example:

mysql> describe students;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | NO   | MUL | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> select * from students where name like '%prajwal%';
+----+------------------+
| id | name             |
+----+------------------+
|  1 | Prajwal Tuladhar |
|  2 | PrAJwal TUladhar |
+----+------------------+
2 rows in set (9.93 sec)

mysql> select * from students where match (name) against ('prajwal');
+----+------------------+
| id | name             |
+----+------------------+
|  1 | Prajwal Tuladhar |
|  2 | PrAJwal TUladhar |
+----+------------------+
2 rows in set (0.08 sec)

Just look at the difference in the time. When full text is used, it only took 0.08 sec while query with like took whooping 9.93 sec. The logic is simple because, indexing the column generally helps to execute the query when using relative criteria. Only disadvantage of using Full Text is that the indexes tend to get larger quickly as they contain one record for each word in each indexed field. It may also depend on which should be given more importance: space or query execution time.

When using like, MySQL has to go through each and every row. And when regular expression is use as pattern matching, MySQL optimizer will never try to optimize the query resulting dismal performance.

Update: I forgot to show the number of rows of the table `students`.

mysql> select count(*) from students;
+----------+
| count(*) |
+----------+
|  1073052 |
+----------+
1 row in set (0.76 sec)