• ’+apple ~macintosh’ 

Find rows that contain the word “apple” , but if the row also contains the word “macintosh” , rate it lower than if row does not. This is “softer”  than a search for ’+apple -macintosh’ , for which the presence of “macintosh”  causes the row not to be returned at all. 

• ’+apple +(>turnover <strudel)’ 

Find rows that contain the words “apple”  and “turnover” , or “apple”  and “strudel”  (in any order), but rank “apple turnover”  higher than “apple strudel” . 

• ’apple*’ 

Find rows that contain words such as “apple” , “apples” , “applesauce” , or “applet” . 

• ’"some words"’ 

Find rows that contain the exact phrase “some words”  (for example, rows that contain “some words of wisdom”  but not “some noise words” ). Note that the "  characters that enclose the phrase are operator characters that delimit the phrase. They are not the quotation marks that enclose the search string itself. 

## Relevancy Rankings for InnoDB Boolean Mode Search 

InnoDB  full-text search is modeled on the Sphinx full-text search engine, and the algorithms used are based on BM25 and TF-IDF ranking algorithms. For these reasons, relevancy rankings for InnoDB boolean full-text search may differ from MyISAM  relevancy rankings. 

InnoDB  uses a variation of the “term frequency-inverse document frequency”  ( TF-IDF ) weighting system to rank a document’s relevance for a given full-text search query. The TF-IDF  weighting is based on how frequently a word appears in a document, offset by how frequently the word appears in all documents in the collection. In other words, the more frequently a word appears in a document, and the less frequently the word appears in the document collection, the higher the document is ranked. 

## How Relevancy Ranking is Calculated 

The term frequency ( TF ) value is the number of times that a word appears in a document. The inverse document frequency ( IDF ) value of a word is calculated using the following formula, where total_records  is the number of records in the collection, and matching_records  is the number of records that the search term appears in. 

${IDF} = log10( ${total_records} / ${matching_records} ) 

When a document contains a word multiple times, the IDF value is multiplied by the TF value: 

${TF} * ${IDF} 

Using the TF  and IDF  values, the relevancy ranking for a document is calculated using this formula: 

## Relevancy Ranking for a Single Word Search 

${rank} = ${TF} * ${IDF} * ${IDF} 

The formula is demonstrated in the following examples. 

This example demonstrates the relevancy ranking calculation for a single-word search. 

mysql> CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, 

