Full-text indexes are user for searching text stored in CHAR, VARCHAR, or TEXT datatypes.

Making a full-text index is quite similar in making indexes of other types.

As an example, let’s create a table called name, indexing its lastname column using
the full-text variant:

CREATE TABLE name (
ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
firstname VARCHAR(75) NOT NULL,
lastname MEDIUMTEXT NOT NULL,
FULLTEXT(lastname),
PRIMARY KEY(ID));

As you can see creating full-text indexes is much like creating other types of indexes.

Note, the retrieval queries based on the full-text index are different. When retrieving data based on full-text indexes, SELECT queries have two special MySQL functions, MATCH() and AGAINST().

So the query to retrieve firstname will be like:

SELECT firstname FROM name WHERE MATCH(lastname) AGAINST('Search Word');

This will lists the rows in which 'Search Word' is found in the lastname column, in order of highest relevance.

In MySQL full-text searches are implemented by sifting through large amounts of natural-language text, it provides a mechanism for retrieving data that produces results that best fit the user’s desired result.If a user were to search using a string like 'Google is the world's most popular search engine', the words 'is' and 'the' will play little or no role in determining result relevance.

MySQL splits search-able text into words, by default eliminating any word of fewer than four characters. These words, along with those found in a predefined list built into the MySQL server, are known as stopwords, or words that should be ignored. However, you can change
the stopword behavior by modifying the following MySQL variables:

ft_min_word_len,ft_max_word_len,ft_max_word_len_for_sort,
ft_stopword_file