Search
Close this search box.
Search
Close this search box.

Full-Text Search

What is MySQL Full Text Search?

A full-text search allows a search of multiple text columns. If you are setting up a search of a series of articles or a site with lots of product-related content, a MySQL FULLTEXT search can make it very easy to find articles or products related to the keywords used by a searcher. This search method does exactly what its name implies–it allows a full search of large text fields.

Compatibility

FULLTEXT searches are only available with a MyISAM table type, which is MySQL’s default table type unless another type is specified. One of the nice things about this type of search is that it supports stop words (search words that are ignored for efficiency) and the results are sorted by relevancy.

Full Text Search with InnoDB tables

InnoDB tables are incompatible with Full Text Search (unless you have an early version of MySQL). So what can you do about it?

You can create a second table using MyISAM that contains only the primary key of the main table and the text content you wish to FULLTEXT search over. Then when you want to do a fulltext search, you do a JOIN between the proper InnoDB table, and the search-bait MyISAM table, with a MATCH condition against the MyISAM table.

This also allows you to store different search-bait text in the MyISAM table to the ‘real’ text in the InnoDB table, which allows you implement features like stemming or special apostrophe/hyphen handling that MySQL's FULLTEXT engine can't manage.

The problem here of course is keeping the MyISAM data consistent with the InnoDB data. You can either run a job every so often to do it, or make the app write to both tables any time there is a text update. Either way, you can get away with inconsistency in fulltext searching, where it would be unacceptable for the ‘canonical’ table data.

Read the original post here.

Making a column(or columns) Full Text compliant

First, you will need to create a FULLTEXT index using one or more text-type data columns (TEXT, CHAR or VARCHAR). An index organizes data and makes it more efficient to find whatever you are searching for. It’s common to set up a search that includes the content in an article, as well as the article title, but other relevant text columns can also be included. Just do not overload the index or the search by including columns that are not absolutely necessary. There are two easy ways to set up a FULLTEXT index with an existing database table–either through an SQL statement or by using phpMyAdmin.

If you like working with MySQL from a command line, the following statement will create an index for an existing table called ‘products’. Both the product name and description columns are included in the index.

ALTER TABLE products ADD FULLTEXT (product_name, product_description);

Important notice

FULLTEXT index indexes can be constructed on multiple columns, allowing searches to be conducted simultaneously on all the indexed columns. However, leftmost index prefixes are not applicable for FULLTEXT indexes. You must construct one index for every column or combination of columns you want to search.

Suppose that you want to search for text sometimes only in column c1 and sometimes in both columns c1 and c2. You must construct two FULLTEXT indexes: one on column c1 and another on columns c1 and c2.

Things to notice

There are a few things you should know about the basic FULLTEXT search.

  • Full Text searches for whole words only, not substrings. You may user a boolean full-text search for more.
  • All MySQL stopwords are ignored in the keyword phrase used in a search. Stopwords are commonly used words that generally do not add anything useful to a search phrase.
  • Alphabetic character case is ignored in a MySQL search, so you do not have to convert anything to all upper or lower case in order to search.
  • Any word found in more than 50% of the rows in the index will be ignored when you use the basic text search.
  • The results will be automatically sorted by relevancy, so the more times the search words appear in a row in the index, the more relevant that particular entry will be.
  • MySQL does not index any words that are 3 or less characters in length, so very short words are ignored.
  • Hyphenated words are treated as separate words.

The query

OK. Lets set up a search to look for a particular product. A FULLTEXT search use MATCH and AGAINST verbs. You will MATCH the column fields AGAINST the text word or phrase you are searching for.

Let’s say that the web site’s product offering is machine tools and you are searching for a drill press. Your query may look like the following:

SELECT * 
FROM products 
WHERE MATCH ( product_name, product_description ) AGAINST ('drill press');

That’s all there is to it. The results should display all rows in the table that refer to drill presses. The results will display in descending order of relevancy, which means the most relevant products will display first.

Boolean Full-Text Searches

As of version 4.0.1, MySQL can perform boolean full-text searches using the IN BOOLEAN MODE modifier. With this modifier, certain characters have special meaning at the beginning or end of words in the search string.

SELECT * 
FROM products 
WHERE MATCH ( product_name, product_description ) AGAINST ('drill*' IN BOOLEAN MODE);

Things to notice

Boolean full-text searches have these characteristics:

  • They do not use the 50% threshold.

  • They do not automatically sort rows in order of decreasing relevance. You can see this from the preceding query result: The row with the highest relevance is the one that contains “MySQL” twice, but it is listed last, not first.

  • They can work even without a FULLTEXT index, although a search executed in this fashion would be quite slow.

  • The minimum and maximum word length full-text parameters apply.

  • The stopword list applies.

See this page for more.

Learning Resources

WordPress Speed Optimization Guide