Using FULLTEXT SearchesMySQL includes the capability for performing full text searches. The full-text search engine allows you to look for words or phrases without using pattern-matching operations. There are three kinds of full-text search:
Full-text search capability is enabled for a given table by creating a special kind of index and has the following characteristics:
The following examples show how to use full-text searching by creating FULLTEXT indexes and then performing queries on them using the MATCH operator. A script to create the table and some sample data to load into it are available in the fulltext directory of the sampdb distribution. A FULLTEXT index is created much the same way as other indexes. That is, you can define it with CREATE TABLE when creating the table initially, or add it afterward with ALTER TABLE or CREATE INDEX. Because FULLTEXT indexes require you to use MyISAM tables, you can take advantage of one of the properties of the MyISAM storage engine if you're creating a new table to use for FULLTEXT searches: Table loading proceeds more quickly if you populate the table first and then add the indexes afterward, rather than loading data into an already indexed table. Suppose that you have a data file named apothegm.txt containing famous sayings and the people to whom they're attributed: Aeschylus Time as he grows old teaches many lessons Alexander Graham Bell Mr. Watson, come here. I want you! Benjamin Franklin It is hard for an empty bag to stand upright Benjamin Franklin Little strokes fell great oaks Benjamin Franklin Remember that time is money Miguel de Cervantes Bell, book, and candle Proverbs 15:1 A soft answer turneth away wrath Theodore Roosevelt Speak softly and carry a big stick William Shakespeare But, soft! what light through yonder window breaks? Robert Burton I light my candle from their torches. If you want to search by phrase and attribution separately or together, you need to index each column separately, and also create an index that includes both columns. You can create, populate, and index a table named apothegm as follows: CREATE TABLE apothegm (attribution VARCHAR(40), phrase TEXT) ENGINE = MyISAM; LOAD DATA LOCAL INFILE 'apothegm.txt' INTO TABLE apothegm; ALTER TABLE apothegm ADD FULLTEXT (phrase), ADD FULLTEXT (attribution), ADD FULLTEXT (phrase, attribution); Natural Language FULLTEXT SearchesAfter setting up the table, perform natural language full-text searches on it using MATCH to name the column or columns to search and AGAINST() to specify the search string. For example: mysql> SELECT * FROM apothegm WHERE MATCH(attribution) AGAINST('roosevelt'); +--------------------+------------------------------------+ | attribution | phrase | +--------------------+------------------------------------+ | Theodore Roosevelt | Speak softly and carry a big stick | +--------------------+------------------------------------+ mysql> SELECT * FROM apothegm WHERE MATCH(phrase) AGAINST('time'); +-------------------+-------------------------------------------+ | attribution | phrase | +-------------------+-------------------------------------------+ | Benjamin Franklin | Remember that time is money | | Aeschylus | Time as he grows old teaches many lessons | +-------------------+-------------------------------------------+ mysql> SELECT * FROM apothegm WHERE MATCH(attribution, phrase) -> AGAINST('bell'); +-----------------------+------------------------------------+ | attribution | phrase | +-----------------------+------------------------------------+ | Alexander Graham Bell | Mr. Watson, come here. I want you! | | Miguel de Cervantes | Bell, book, and candle | +-----------------------+------------------------------------+ In the last example, note how the query finds records that contain the search word in different columns, which demonstrates the FULLTEXT capability of searching multiple columns at once. Also note that the order of the columns as named in the query is attribution, phrase. That differs from the order in which they were named when the index was created (phrase, attribution), which illustrates that order does not matter. What matters is that there must be some FULLTEXT index that consists of exactly the columns named. If you just want to see how many records a search matches, use COUNT(*):
mysql> SELECT COUNT(*) FROM apothegm WHERE MATCH(phrase) AGAINST('time');
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
Output rows for natural language FULLTEXT searches are ordered by decreasing relevance when you use a MATCH expression in the WHERE clause. Relevance values are non- negative floating point values, with zero indicating "no relevance." To see these values, use a MATCH expression in the output column list: mysql> SELECT phrase, MATCH(phrase) AGAINST('time') AS relevance -> FROM apothegm; +-----------------------------------------------------+-----------------+ | phrase | relevance | +-----------------------------------------------------+-----------------+ | Time as he grows old teaches many lessons | 1.3253291845322 | | Mr. Watson, come here. I want you! | 0 | | It is hard for an empty bag to stand upright | 0 | | Little strokes fell great oaks | 0 | | Remember that time is money | 1.3400621414185 | | Bell, book, and candle | 0 | | A soft answer turneth away wrath | 0 | | Speak softly and carry a big stick | 0 | | But, soft! what light through yonder window breaks? | 0 | | I light my candle from their torches. | 0 | +-----------------------------------------------------+-----------------+ A natural language search finds records that contain any of the search words, so a query such as the following returns records with either "hard" or "soft": mysql> SELECT * FROM apothegm WHERE MATCH(phrase) -> AGAINST('hard soft'); +---------------------+-----------------------------------------------------+ | attribution | phrase | +---------------------+-----------------------------------------------------+ | Benjamin Franklin | It is hard for an empty bag to stand upright | | Proverbs 15:1 | A soft answer turneth away wrath | | William Shakespeare | But, soft! what light through yonder window breaks? | +---------------------+-----------------------------------------------------+ Boolean Mode FULLTEXT SearchesGreater control over multiple-word matching can be obtained by using boolean mode FULLTEXT searches. This type of search is performed by adding IN BOOLEAN MODE after the search string in the AGAINST() function. Boolean searches have the following characteristics:
Query Expansion FULLTEXT SearchesA full-text search with query expansion performs a two-phase search. The initial search is like a regular natural language search. Then the most highly relevant records from this search are used for the second phase. The words in these records are used along with the original search terms to perform a second search. Because the set of search terms is larger, the result generally includes records that are not found in the first phase but are related to them. To perform this kind of search, add WITH QUERY EXPANSION following the search terms. The following example provides an illustration. The first query shows a natural language search. The second query shows a query expansion search. Its result includes an extra record that contains none of the original search terms. This record is found because it contains the word "candle" that is present in one of the records found by the natural language search. mysql> SELECT * FROM apothegm -> WHERE MATCH(attribution, phrase) -> AGAINST('bell book'); +-----------------------+------------------------------------+ | attribution | phrase | +-----------------------+------------------------------------+ | Miguel de Cervantes | Bell, book, and candle | | Alexander Graham Bell | Mr. Watson, come here. I want you! | +-----------------------+------------------------------------+ mysql> SELECT * FROM apothegm -> WHERE MATCH(attribution, phrase) -> AGAINST('bell book' WITH QUERY EXPANSION); +-----------------------+---------------------------------------+ | attribution | phrase | +-----------------------+---------------------------------------+ | Miguel de Cervantes | Bell, book, and candle | | Alexander Graham Bell | Mr. Watson, come here. I want you! | | Robert Burton | I light my candle from their torches. | +-----------------------+---------------------------------------+ Configuring the FULLTEXT Search EngineSeveral full-text parameters are configurable and can be modified by setting system variables. The parameters that determine the shortest and longest words to index in FULLTEXT indexes are ft_min_word_len and ft_max_word_len. Words with lengths outside the range defined by these two variables are ignored when FULLTEXT indexes are built. The default minimum value is 4. The default maximum value depends on your server version. Currently in MySQL 4.1, the maximum is 84. Suppose that you want to change the minimum word length from 4 to 3. Do so like this:
For more information on setting system variables, see Appendix D. For details on using option files, see Appendix F. Note: If you use myisamchk to rebuild indexes for a table that contains any FULLTEXT indexes, see the FULLTEXT-related notes in the myisamchk description in Appendix F. |