MySQL - Boolean Fulltext Search

MySQL: Boolean Full-Text Searches

Introduction to Full-Text Searching

Full-text searching provides a powerful way to perform complex searches against character-based data in your database. Unlike simple LIKE '%word%' queries, a full-text search is optimized for natural language, understands word boundaries, and can rank results by relevance. MySQL offers this functionality through FULLTEXT indexes.

What is Boolean Full-Text Search?

Boolean full-text search is a specific mode that lets you build highly detailed and complex search queries using special operators. Instead of just searching for a phrase, you can demand that certain words must be present, some must be absent, and you can rank others higher or lower.

This mode is activated by adding the IN BOOLEAN MODE modifier to your AGAINST() expression. Unlike natural language searches, results in boolean mode are not automatically sorted by relevance unless you also calculate a relevance score and order by it.

Setting Up a FULLTEXT Index

Before you can perform a full-text search, you must create a FULLTEXT index on the columns you want to search. This is typically done when creating the table. We will use the InnoDB storage engine, which is the modern default.

CREATE TABLE ARTICLES (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    ARTICLE_TITLE VARCHAR(255),
    ARTICLE_BODY TEXT,
    FULLTEXT KEY ft_index (ARTICLE_TITLE, ARTICLE_BODY)
) ENGINE=InnoDB;

INSERT INTO ARTICLES (ARTICLE_TITLE, ARTICLE_BODY) VALUES
('Introduction to SQL', 'SQL is a powerful language for relational database management.'),
('Advanced MySQL Techniques', 'This guide covers advanced MySQL features like indexing and transactions.'),
('Learning Java Basics', 'Java is a versatile, object-oriented programming language.'),
('Data Structures in Java', 'Understanding data structures is key to efficient programming.'),
('Getting Started with Databases', 'A database allows for structured storage and retrieval of data.');

Boolean Mode Syntax

SELECT columns, MATCH(indexed_columns) AGAINST('expression' IN BOOLEAN MODE) AS relevance
FROM table_name
WHERE MATCH(indexed_columns) AGAINST('expression' IN BOOLEAN MODE);

The MATCH() clause specifies the indexed columns to search, and AGAINST() contains your search query.

Key Boolean Operators

Operator	Description	Example
+	The word MUST be present in each returned row.	+MySQL +advanced
-	The word MUST NOT be present in any returned row.	Java -SQL
(no operator)	The word is optional, but rows containing it are rated higher.	database language
>	Increases the word’s contribution to the relevance score.	>database language
<	Decreases the word’s contribution to the relevance score.	<database language
~	Negates the word’s contribution to the relevance score. Useful for ranking junk words lower, but does not exclude the row.	database ~management
*	Wildcard operator. It must be appended to the end of a word.	program*
” “	Defines a literal phrase. The words must appear in this exact order.	”relational database”
()	Groups words into a sub-expression for complex logic.	+(database >management)

Practical Search Examples

Using the ARTICLES table we created:

Example 1: Must contain ‘database’, must not contain ‘SQL’

SELECT ARTICLE_TITLE, ARTICLE_BODY FROM ARTICLES
WHERE MATCH(ARTICLE_TITLE, ARTICLE_BODY)
AGAINST('+database -SQL' IN BOOLEAN MODE);

This would return the article ‘Getting Started with Databases’ but exclude ‘Introduction to SQL’.

Example 2: Find articles about ‘Java’ or ‘SQL’

SELECT ARTICLE_TITLE FROM ARTICLES
WHERE MATCH(ARTICLE_TITLE, ARTICLE_BODY)
AGAINST('Java SQL' IN BOOLEAN MODE);

Without operators, the words are optional. This finds any article containing either ‘Java’ or ‘SQL’ (or both).

Example 3: Find words starting with ‘program’

SELECT ARTICLE_TITLE, ARTICLE_BODY FROM ARTICLES
WHERE MATCH(ARTICLE_TITLE, ARTICLE_BODY)
AGAINST('program*' IN BOOLEAN MODE);

This will match both ‘programming’ and ‘program’.

Important Considerations for InnoDB

• Indexing: A FULLTEXT index is mandatory for the columns in the MATCH() clause.
• Stopwords: MySQL maintains a list of common ‘stopwords’ (like ‘a’, ‘the’, ‘is’) that are ignored during indexing. You can customize this list.
• Minimum Word Length: By default, words shorter than 3 characters (innodb_ft_min_token_size) are not indexed. You may need to adjust this setting and rebuild your index for it to take effect.
• 50% Threshold: This rule, which ignored words present in over 50% of rows, applied to the older MyISAM engine. In InnoDB, this is no longer a hard-coded limitation.

Implementing Boolean Search in Client Applications

To safely use user input in a boolean full-text search, you should sanitize it and use prepared statements.

Node.js (using mysql2/promise)

const mysql = require('mysql2/promise');

async function searchArticles(query) {
  let connection;
  try {
    connection = await mysql.createConnection({ /* connection details */ });

    // Basic sanitation: ensure user query has required terms.
    // A real application would need more robust sanitation.
    const sanitizedQuery = `+${query.replace(/\s+/g, ' +')}`;

    const sql = `SELECT ARTICLE_TITLE, MATCH(ARTICLE_TITLE, ARTICLE_BODY)
                 AGAINST(? IN BOOLEAN MODE) AS score
                 FROM ARTICLES
                 WHERE MATCH(ARTICLE_TITLE, ARTICLE_BODY)
                 AGAINST(? IN BOOLEAN MODE)
                 ORDER BY score DESC`;

    const [rows] = await connection.execute(sql, [sanitizedQuery, sanitizedQuery]);
    console.log('Search Results:');
    console.log(rows);

  } catch (error) {
    console.error('Search failed:', error);
  } finally {
    if (connection) await connection.end();
  }
}

// Example usage: find articles that MUST contain 'MySQL' and 'advanced'
searchArticles('MySQL advanced');