Using a 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. A FULLTEXT search is easy to set up if you just follow these simple instructions.





Basic FULLTEXT search has been available in MySQL since version 3.23.23. MySQL version 4 introduced more complex fulltext search functions that include boolean searches. This MySQL tutorial covers basic text searches, which is the type of search most commonly used. MySQL refers to this simple type of search as Natural Language Full-Text Searches.



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.



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);  

You can also use phpMyAdmin to create the index.

1. Select the table, go to the index section of the Table page, enter the number of columns you wish to index, and click Go.
2. On the Create a New Index page, select FULLTEXT as the index type, then select the columns you want to include in the index. You can name the index if you wish, but it is not necessary.
3. Click Save.

There are a few things you should know about the basic FULLTEXT search. First, 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. Second, 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. Third, any word found in more than 50% of the rows in the index will be ignored when you use the basic text search. Fourth, 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. Fifth, remember that MySQL does not index any words that are 3 or less characters in length, so very short words are ignored. Sixth, hyphenated words are treated as separate words.



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.



Useful link:

• http://devzone.zend.com/node/view/id/1304
• http://dev.mysql.com/doc/refman/5.0/en/fulltext-query-expansion.html