Understanding AGAINST in MySQL: Full-Text Search Explained

MySQL provides powerful full-text search capabilities that allow you to efficiently search through text data in your tables. One of the most important keywords used in full-text search is AGAINST, which works in combination with the MATCH() function.

🔍 Basic Syntax

SELECT * 
FROM table_name
WHERE MATCH(column1, column2, ...) 
      AGAINST ('search_text' [IN NATURAL LANGUAGE MODE | IN BOOLEAN MODE]);

This syntax enables full-text searching across one or more columns.

📋 Requirements

1. Full-text index must be added to the columns you want to search.

2. Only CHAR, VARCHAR, or TEXT column types are supported.

3. Works with the InnoDB and MyISAM storage engines.

You can add a full-text index like this:

ALTER TABLE articles ADD FULLTEXT(title, body);

💡 Example Usage

SELECT * 
FROM articles
WHERE MATCH(title, body) 
      AGAINST ('database');

This query searches for the word “database” in the title and body columns of the articles table.

🧭 Search Modes

MySQL supports two major modes for full-text search:

✅ Natural Language Mode (default)

This mode searches in a human-friendly way, similar to how users naturally type in queries.

SELECT * 
FROM articles 
WHERE MATCH(title) 
      AGAINST('mysql tutorial' IN NATURAL LANGUAGE MODE);

• Common stop words (e.g. "the", "is") are ignored.

• Case insensitive.

✅ Boolean Mode

This mode allows the use of logical operators and special syntax to fine-tune your search.

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

This returns rows that must contain "mysql" and must not contain "oracle".

📊 Relevance Score

In Natural Language Mode, MySQL calculates a relevance score based on how well each row matches the search terms. You can sort results by this score:

SELECT *, MATCH(title, body) AGAINST('mysql') AS score
FROM articles
WHERE MATCH(title, body) AGAINST('mysql')
ORDER BY score DESC;

⚠️ Limitations

• Words shorter than 4 characters are ignored by default (ft_min_word_len).

• Stop words can prevent some common words from being searched.

• Limited support for non-English or Asian languages (you may need a tokenizer or external tools like Elasticsearch or Sphinx for better results).

Conclusion

The MATCH ... AGAINST syntax in MySQL provides an effective way to perform full-text searches. By understanding how it works in both natural language and boolean modes, you can write more precise and powerful queries.

If you're working with a multilingual database or complex search features, consider integrating external full-text search engines. But for many applications, MySQL's built-in support is more than sufficient.