mysql match against ~ example

mysql match against ~ example

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

MySQL MATCH AGAINST Precision Issues with Multi-word Queries

When executing a full-text search query like SELECT * FROM tbl WHERE MATCH(hotel) AGAINST('the mill hotel' IN BOOLEAN MODE), it is common to encounter scenarios where the query ignores specific multi-word phrases. If your table returns ten unrelated hotels instead of the "Mill Hotel," the issue likely stems from how MySQL's default full-text search index tokenizes and ranks documents.

By default, the MATCH function performs a relevance ranking based on term frequency (TF) and inverse document frequency (IDF). The engine counts how many times each individual word appears in the target column relative to its appearance across the entire table. In your specific case, if "mill" or "hotel" appears frequently in other rows—perhaps describing amenities like "a nice mill view" or generic hotel descriptions—the term weight for those words becomes diluted. Consequently, the query matches rows containing those individual tokens rather than rows where the specific combination of terms exists together.

To force MySQL to treat "the mill hotel" as a single unit of meaning, you must use Boolean Mode with proximity operators. Specifically, the NEAR operator allows you to define a window size around the terms. This ensures that the words appear within a certain number of columns of each other in the same row.

You can modify your query to explicitly require the terms to be near one another:

SELECT * 
FROM tbl 
WHERE MATCH(hotel) AGAINST('+the +mill +hotel' IN BOOLEAN MODE)
    AND LOCATE('mill', hotel) > 0
LIMIT 10;

However, a more robust approach for phrase matching relies on the NEAR operator or simply ensuring the terms are adjacent. While standard MATCH ... AGAINST does not support a direct "phrase" syntax like '"the mill hotel"' in the query string itself without specific configuration, you can utilize the + prefix to require every term and then refine the logic.

A more effective strategy involves using the NEAR operator if available in your MySQL version, or restructuring the query to rely on proximity:

SELECT * 
FROM tbl 
WHERE MATCH(hotel) AGAINST('+mill +hotel' IN BOOLEAN MODE)
    AND INSTR(hotel, 'the mill hotel') > 0
LIMIT 10;

The INSTR function acts as a safety net to ensure the exact string exists in the column, bypassing the tokenization issues of the full-text index for this specific phrase. While this adds a computational cost by scanning the raw text column, it guarantees accuracy when the relevance ranking fails to prioritize the specific entity name.

Another critical consideration is whether your hotel column is indexed as FULLTEXT. If you attempt to run this query on a standard B-tree index, MySQL will ignore the MATCH clause entirely and fall back to standard SQL behavior, which might not return full-text results at all unless the column type supports it (e.g., TEXT, LONGTEXT, or VARCHAR with a FULLTEXT index). Verify your table structure:

SHOW INDEX FROM tbl;
-- Check if 'hotel' has a Fulltext key

If the column lacks a full-text index, you must create one before these queries will function as intended. Additionally, ensure that common stop words like "the" are not being ignored by your specific MySQL configuration. By default, MySQL ignores common words to save processing power, but if "the" is significant for your search logic, you may need to adjust the innodb_ft_min_token_size or configure a custom stopword list, though this is often unnecessary unless "the" is being dropped entirely.

In many production environments, developers find that hybrid search strategies yield better results than relying solely on full-text indexing. Combining boolean mode with string functions like LOCATE or INSTR provides the precision needed to isolate specific names like "Mill Hotel" while still leveraging the speed of full-text indexes for broader searches.

For applications built on Laravel, you might consider using packages that abstract these complexities, such as the laravelcompany.com library resources, which often provide optimized search helpers. These tools can help manage the trade-off between index speed and query precision without requiring complex SQL modifications in every controller or model.

Our network