MySQL Full-Text Search on a Specific Column

Performing a full-text search on a specific column in MySQL allows you to search for complex text patterns and keywords within large datasets much faster than using traditional LIKE queries. This article provides a direct, step-by-step guide on how to enable full-text indexing on a single column and execute queries using the MATCH() ... AGAINST() syntax in both Natural Language and Boolean modes.

Step 1: Create a FULLTEXT Index on the Column

Before you can perform a full-text search, you must define a FULLTEXT index on the specific column you want to query. You can add this index to an existing table using the ALTER TABLE statement.

ALTER TABLE articles ADD FULLTEXT(content);

In this example, articles is the table name, and content is the specific column being indexed. Note that MySQL supports FULLTEXT indexes on CHAR, VARCHAR, and TEXT columns in both InnoDB and MyISAM storage engines.

Step 2: Perform a Basic Full-Text Search

Once the index is created, you can query the specific column using the MATCH() and AGAINST() functions. The MATCH() function specifies the indexed column, and AGAINST() defines the search term.

SELECT id, title, content 
FROM articles 
WHERE MATCH(content) AGAINST('database');

By default, MySQL performs this search in Natural Language Mode. It looks for rows containing the word “database” and automatically ranks them by relevance, returning the most relevant rows first.

Step 3: Perform an Advanced Search Using Boolean Mode

For more control over your search criteria, you can use Boolean Mode. This mode allows you to use operators to require, exclude, or define wildcards for specific words.

To search in Boolean Mode, add IN BOOLEAN MODE inside the AGAINST() function:

SELECT id, title, content 
FROM articles 
WHERE MATCH(content) AGAINST('+mysql -oracle' IN BOOLEAN MODE);

Common Boolean Operators:

Key Considerations