Versions of MySQL from 3.23.23 on include 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. This capability is enabled for a given table by creating a special kind of index and has the following characteristics:
Some of these features require more recent versions of MySQL than others. The following table shows the versions at which FULLTEXT features were introduced:
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 FULLTEXT index is created 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 handler if you're creating a new table to use for FULLTEXT searches: Table loading proceeds more quickly if you populate the table and then add the indexes afterward rather than loading data into an already indexed table. Suppose 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? If you want to search by phrase and attribution separately or together, you need to index the columns 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); LOAD DATA LOCAL INFILE 'apothegm.txt' INTO TABLE apothegm; ALTER TABLE apothegm ADD FULLTEXT (phrase), ADD FULLTEXT (attribution), ADD FULLTEXT (phrase, attribution); After setting up the table, perform 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 | +----------+ By default, output rows for 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 column output list: mysql> SELECT phrase, MATCH(phrase) AGAINST('time') AS relevance -> FROM apothegm; +-----------------------------------------------------+-----------------+ | phrase | relevance | +-----------------------------------------------------+-----------------+ | Time as he grows old teaches many lessons | 1.1976701021194 | | 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.2109839916229 | | 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 | +-----------------------------------------------------+-----------------+ By default, a search finds records that contain any of the search words, so a query like the following will return 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? | +---------------------+-----------------------------------------------------+ Greater control over multiple-word matching can be obtained as of MySQL 4.0.1, when support was added for 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:
A trailing asterisk acts as a wildcard so that any record containing words beginning with the search word match. For example, 'soft*' matches "soft," "softly," "softness," and so on: mysql> SELECT * FROM apothegm WHERE MATCH(phrase) -> AGAINST('soft*' IN BOOLEAN MODE); +---------------------+-----------------------------------------------------+ | attribution | phrase | +---------------------+-----------------------------------------------------+ | Proverbs 15:1 | A soft answer turneth away wrath | | William Shakespeare | But, soft! what light through yonder window breaks? | | Theodore Roosevelt | Speak softly and carry a big stick | +---------------------+-----------------------------------------------------+ However, the wildcard feature cannot be used to match words shorter than the minimum index word length. The full set of modifiers is listed in the entry for MATCH in Appendix C, "Operator and Function Reference."
Prior to MySQL 4, FULLTEXT search parameters can be modified only by making changes to the source code and recompiling the server. MySQL 4 provides several configurable parameters that can be modified by setting server variables. The two that are of most interest are ft_min_word_len and ft_max_word_len, which determine the shortest and longest words that will be indexed. The default values are 4 and 254; words with lengths outside that range are ignored when FULLTEXT indexes are built. Suppose you want to change the minimum word length from 4 to 3. Do so like this:
For more information on option files and setting server variables, see Appendix D. |