CC SQL 18-full text searching

The following CREATE statement demonstrates the use of the FULLTEXT clause:

• Input

• CREATE TABLE productnotes
• (
• note_id int NOT NULL AUTO_INCREMENT,
• prod_id char(10) NOT NULL,
• note_date datetime NOT NULL,
• note_text text NULL ,
• PRIMARY KEY(note_id),
• FULLTEXT(note_text)
• ) ENGINE=MyISAM;

• Analysis

We'll look at the CREATE TABLE statement in detail in Chapter 21. For now, just note that this CREATE TABLE statement defines table productnotes and lists the columns that it is to contain. One of those columns is named note_text, and it is indexed by MySQL for full-text searching as instructed by the clause FULLTEXT(note_text). Here FULLTEXT indexes a single column, but multiple columns may be specified if needed.

Once defined, MySQL automatically maintains the index. When rows are added, updated, or deleted, the index is automatically updated accordingly.

FULLTEXT may be specified at table creation time, or later on (in which case all existing data would have to be immediately indexed).

After indexing, full-text searches are performed using two functions: Match() to specify the columns to be searched and Against() to specify the search expression to be used.

Here is a basic example:

• Input

• SELECT note_text
• FROM productnotes
• WHERE Match(note_text) Against('rabbit');

• Analysis

The SELECT statement retrieves a single row, note_text. For the WHERE clause, a full-text search is performed. Match(note_text) instructs MySQL to perform the search against that named column, and Against('rabbit') specifies the wordrabbit as the search text. As two rows contained the word rabbit, those two rows were returned.

Query expansion is used to try to widen the range of returned full-text search results. Consider the following scenario. You want to find all notes with references toanvils in them. Only one note contains the word anvils, but you also want any other rows that may be related to your search, even if the specific word anvils is not contained within them.

This is a job for query expansion. When query expansion is used, MySQL makes two passes through the data and indexes to perform your search:

1. First, a basic full-text search is performed to find all rows that match the search criteria.

2. Next, MySQL examines those matched rows and selects all useful words (we'll explain how MySQL figures out what is useful and what is not shortly).

3. Then, MySQL performs the full-text search again, this time using not just the original criteria, but also all of the useful words.

• Input

• SELECT note_text
• FROM productnotes
• WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);

• MySQL supports an additional form of full-text searching called boolean mode. In Boolean mode you may provide specifics as to
• 1. Words to be matched
• 2. Words to be excluded (if a row contained this word it would not be returned, even though other specified words were matched)
• 3. Ranking hints (specifying which words are more important than others so they can be ranked higher)
• 4. Expression grouping
• 5. And more

• SELECT note_text
• FROM productnotes
• WHERE Match(note_text) Against('heavy rope*' IN BOOLEAN MODE);

• Analysis

This time only one row is returned. Again, the word heavy is matched, but this timerope* instructs MySQL to explicitly exclude any row that contains rope* (any word beginning with rope, including ropes, which is why one of the rows was excluded)

• Table 18.1. Full-Text Boolean
• Operators

• Privilege Description
• + Include, word must be present.
• - Exclude, word must not be present
• .> Include, and increase ranking value.
• < Include, and decrease ranking value.
• () Group words into subexpressions (allowing them to be included, excluded, ranked, and so forth as a group).
• ~ Negate a word's ranking value.
• * Wildcard at end of word.
• "" Defines a phrase (as opposed to a list of individual words, the entire phrase is matched for inclusion or exclusion).