Full Text Search

This whitepaper explores advantages of full-text search in MySQL focusing on the features, search approaches and indexing.

Straight forward approach to searching data in a relational DBMS is based on a LIKE operator. It allows to search substring in a string specifying either direct match or the search pattern using percentage signs acting as wildcards.

For example, the following query fetches rows where "comments" field is equal to none:

SELECT * FROM journal WHERE comments LIKE 'none';

To search a substring, enclose search pattern in percentage signs as follows:

SELECT * FROM journal WHERE comments LIKE '%none%';

Note that MySQL LIKE operator is case insensitive. If you need case sensitive search use LIKE BINARY.

As the data grows, traditional search methods may encounter performance issues, leading to the idea of full-text search.

Full Text Search Features

MySQL supports three types of full-text searches:

• Natural Language. Natural language search treats the search string as a phrase in terms of human language. This mode does not have any special operators except of double quote characters. "Natural Language" is the default mode of full-text search. It also can be specified directly by IN NATURAL LANGUAGE MODE modifier inside AGAINST function.
• Query Expansion. This is a variation of the previous search mode. First, MySQL does natural language search over the search string. Then it takes words from the most relevant search results, adds them to new search string and searches again. This mode of full-text search must be specified by WITH QUERY EXPANSION modifier inside AGAINST function.
• Boolean. This kind of full-text search interprets the search pattern using the special query language rules. The search string can contain the words to search as well as operators specifying that a word must be present or absent in matching rows and its weight. Boolean mode of full-text search must be specified by IN BOOLEAN MODE modifier inside AGAINST function.

Full-text search offers important benefits compared to LIKE conditional operator:

• Flexibility. Unlike the basic matching capabilities implemented by LIKE, full-text search provides more advanced features such as semantic proximity. This allows to build context-aware queries implementing text search in a natual language style.
• Performance. Performance of search via LIKE operator decreases as the data expands. MySQL full-text search is significantly faster than LIKE operator, especially on large datasets.
• Precision. Although LIKE condition offers ultimate precision, it may miss relevant results as it does not care about semantics. On another hand, full-text search provides balance between precision and recall through the flexibility of configuration settings.

Using Full Text Search in Queries

MySQL offers 2 special functions for a full-text search: MATCH and AGAINST. Function MATCH() accepts a comma-separated list of columns involved full-text search, and function AGAINST() specifies word or phrase to search. For example, the queury from example above may be rewritten using full-text search as follows:

SELECT id, title, comments FROM journal 
WHERE MATCH(title, comments) 
AGAINST ('none' IN NATURAL LANGUAGE MODE);

Full-Text Indexes

MySQL offers special type of index FULLTEXT to increase the efficiency of text searches queries. This kind of indexes can only be used with InnoDB or MyISAM table types and it can be built on CHAR, VARCHAR or TEXT columns.

FULLTEXT index might be included in the CREATE TABLE statement:

CREATE TABLE journal (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    comments TEXT,
    FULLTEXT KEY (title, comments)
);

Also, it can be created in a separate ALTER TABLE or CREATE FULLTEXT INDEX statement.

Have questions? Contact us