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.
- Select the table, go to the index section of the Table page, enter the number of columns you wish to index, and click Go.
- 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.
- Click Save.
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.