MySQL Fulltext Search

One of the most powerful features of MySQL is the fulltext index. The fulltext index allows text searching in a very effective manner. A fulltext index can be created for TEXT, CHAR, and VARCHAR fields.

Now, let's create a sample table and use this table to illustrate the features of the fulltext index.

CREATE TABLE sample_table (
 name TEXT,
 FULLTEXT (name)
)

Let's add some sample data to the table.

INSERT INTO sample_table VALUES
 ("Brad Pitt"),
 ("Cameron Diaz"),
 ("Clint Eastwood"),
 ("Alec Baldwin"),
 ("Kyra Sedgwick")

If you have already created the table you can add a fulltext index with the ALTER TABLE statement:

ALTER TABLE sample_table ADD FULLTEXT(name)

Search for text:

SELECT * FROM sample_table WHERE MATCH(name) AGAINST('Pitt')

Will return 1 match (Brad Pitt)

SELECT * FROM sample_table WHERE MATCH(name) AGAINST('Alec Clint')

Will return 2 matches (Alec Baldwin and Clint Eastwood)

Features of Fulltext Search:

  • Excludes words less than 4 characters in length and greater than 84 characters in length
    ( can be changed by modifying the ft_min_word_len and ft_max_word_len variables )
  • Excludes partial words
  • Excludes words that appear in more than half of the rows
  • Words in the stopword list are also excluded from the search results
    ( these are common words such as a, an, the, etc... )
  • Hyphenated words are treated as two words
  • By default, results are returned in order of relevance, from highest to lowest, unless you include the ORDER BY clause

For more information about fulltext search please visit the following resources:

posted August 30, 2009 in MySQL