The Three Search Modes of MySQL FULLTEXT Indexes

When it comes to MySQL queries against columns containing text, FULLTEXT indexes offer a variety of approaches that go beyond your basic pattern matching. When creating a FULLTEXT index for querying text data, we can use MATCH and AGAINST along with our choice of three modes. We have Natural Language Mode, which is the default. Then we have Boolean Mode, which is helpful for more specific searches as it relates to inclusions and exclusions. Last but not least, we have Query Expansion Mode, which can be used to find relevant data based on what is searched for in the query.

Let’s look at a few examples utilizing FULLTEXT indexes.

Setting Up Our Test Data

We’ll create a Comics table and our FULLTEXT index:

CREATE TABLE Comics (
Id INT AUTO_INCREMENT PRIMARY KEY,
Title VARCHAR(255),
Author VARCHAR(255),
Description TEXT,
FULLTEXT(Title, Author, Description)
);

Then add some test data:

INSERT INTO comics (Title, Author, Description) VALUES
('The Amazing Spider-Man', 'Stan Lee', 'The story of Peter Parker, a teenager who gains spider-like abilities after being bitten by a radioactive spider.'),
('Batman: Year One', 'Frank Miller', 'A retelling of Batman\'s origin, focusing on Bruce Wayne\'s first year as the Dark Knight.'),
('The Dark Knight Returns', 'Frank Miller', 'An aging Bruce Wayne comes out of retirement to don the mantle of Batman once more.'),
('Watchmen', 'Alan Moore', 'A complex, multi-layered story set in an alternate history where superheroes emerged in the 1940s and 1960s.'),
('X-Men: Days of Future Past', 'Chris Claremont', 'A story involving time travel, where the X-Men must prevent a dystopian future ruled by Sentinels.'),
('Sandman: Preludes & Nocturnes', 'Neil Gaiman', 'The first volume in the Sandman series, where Dream escapes from captivity and seeks to reclaim his lost objects of power.'),
('Maus', 'Art Spiegelman', 'A graphic novel depicting the author\'s father\'s experiences during the Holocaust.'),
('V for Vendetta', 'Alan Moore', 'Set in a dystopian future, a masked vigilante known as V fights against a totalitarian regime.');

Natural Language Mode

Let’s say we want to do a simple search for records that contain Batman and put our FULLTEXT index to good use. We’ll specify MATCH and AGAINST and Natural Language Mode will be used as the default:

SELECT * FROM Comics
WHERE MATCH(Title, Author, Description) AGAINST('Batman');

This returns two records. One record found ‘Batman’ in the Title for Id 2 and one record found ‘Batman’ in the Description for Id 3.

To confirm our FULLTEXT index was used, we can use EXPLAIN:

EXPLAIN SELECT * FROM Comics
WHERE MATCH(Title, Author, Description) AGAINST('Batman');

We can see from the type column that we used the FULLTEXT index.

Boolean Mode

Boolean Mode gets its name because we can take advantage of characters to be more specific with our search. The MySQL documentation has a full list here.

Perhaps we want to do a more precise search for Batman, and we’re feeling young and want some early Batman compared to old Batman. We can use the following query in Boolean Mode and specify that we want Batman (with the “+”) but we want to exclude any references to retirement (with the “-“).

SELECT * FROM Comics
WHERE MATCH(Title, Author, Description) AGAINST('+Batman -retirement' IN BOOLEAN MODE);

Now we only have our comic with Batman’s origin story and the record mentioning ‘retirement’ is excluded.

Query Expansion Mode

Finally, let’s go over an example using Query Expansion Mode. I find this one to be tricky, so I’ll reference the documentation for how it works:

“It works by performing the search twice, where the search phrase for the second search is the original search phrase concatenated with the few most highly relevant documents from the first search. Thus, if one of these documents contains the word “databases” and the word “MySQL”, the second search finds the documents that contain the word “MySQL” even if they do not contain the word “database”.”

For our example, we’ll add a column for relevance in our query to see how relevant our result is to our search term. Let’s do a search for ‘first’ and see what we get:

SELECT Title, Author, Description,
MATCH(Title, Author, Description) AGAINST('first' WITH QUERY EXPANSION) AS relevance
FROM Comics
WHERE MATCH(Title, Author, Description) AGAINST('first' WITH QUERY EXPANSION)
ORDER BY relevance DESC;

Why did we get the results we did? The word “first” shows up in the description of Id 6 and 2, so those records have the highest relevance. From there, MySQL expands from keywords found in the initial results to find potentially related records. Even though the word ‘first’ wasn’t found in the last two records returned, it found them to have some relevance based on the initial search results.

Taking Advantage of FULLTEXT Indexes

You have three options when making use of MySQL FULLTEXT indexes: Natural Language Mode, Boolean Mode, and Query Expansion Mode. Using MATCH and AGAINST to query your FULLTEXT index gives you options on how simple or complex you want your results to be. If you find yourself struggling with basic searches against text in MySQL, try taking advantage of the flexibility FULLTEXT indexes can provide.

Thanks for reading!