Match against query not working - incorrect parameters

Question

I have a table with articles and a table with categories. Each category has a number of keywords and I want to use those keywords to determine if an article belongs to a certain category.

I'm using the query below:

SELECT 
  path,
  title,
  description,
  keywords
FROM
  (
  SELECT 
    path, 
    keywords, 
    (select title from article where id = 164016) as title,
    (select description from article where id = 164016) as description
  FROM 
    categories c 
) as x
WHERE 
  MATCH (title, description) AGAINST ('my keywords' IN BOOLEAN MODE)  

For some reason this query is not working because of incorrect paramaters with MATCH but I can't figure out what it is.

Answer 1

Use boolean fulltext search to enable exact matching on an expression. However, there are some limitations to such searches, as described by the documentation linked above:

A phrase that is enclosed within double quote (") characters matches only rows that contain the phrase literally, as it was typed. The full-text engine splits the phrase into words and performs a search in the FULLTEXT index for the words. Nonword characters need not be matched exactly: Phrase searching requires only that matches contain exactly the same words as the phrase and in the same order. For example, "test phrase" matches "test, phrase".

If the phrase contains no words that are in the index, the result is empty. The words might not be in the index because of a combination of factors: if they do not exist in the text, are stopwords, or are shorter than the minimum length of indexed words.

The above also means that if you have a stop word or a word shorter than the minimum length in the search expression, then MySQL will not return any matches.

SELECT path, keywords, MATCH (c.keywords) AGAINST ('"Here is some text"' IN BOOLEAN MODE) as relevance
FROM categories c
WHERE MATCH (c.keywords) AGAINST ('"Here is some text"' IN BOOLEAN MODE) 

If you want completely exact matches, then you cannot use fulltext search. You either need to use the like operator or the = operator.

UPDATE

1. title in this case is a calculated field which does not have a fulltext index.

2. against()

takes a string to search for, and an optional modifier that indicates what type of search to perform. The search string must be a string value that is constant during query evaluation. This rules out, for example, a table column because that can differ for each row.

(source)

You have a field name in the against() function, which is not allowed.