MySQL Full-text Search Function
MySQL 4.0 has come up with a completely functional method to employ text searches on the table columns. In fact, the method known as full-text search function is capable of building a small search engine to conduct searches on CHAR, VARCHAR and TEXT columns. Though the function was added in the MySQL 3.23.23 version, but full capabilities of this function could be explored in MySQL 4.0.
Full-Text Search Defined
Suppose you want to create a table with few text fields like first name, last name and profile. You can provide space to mysql full-text functionality in the following manner:
-
-
CREATE TABLE person (id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,firstName VARCHAR(200),lastName VARCHAR(200),profile TEXT,FULLTEXT (firstName, lastName, profile)) Engine=MyIsam;
Alternatively, if you already have a table and want to add full-text functionality to it, you may follow a similar code as given below:
-
-
ALTER TABLE person ADD FULLTEXT (firstName, lastName, profile);
These lines help the MySQL to set up an index on text fields of firstName, lastName and profile. This enhances the capability of MySQL to refine its search criterions, whenever a user tries to search for textual data on a website.
After successful implementation of Full Text is done, one may follow the following syntax to carry out the search:
-
-
SELECT col1, col2 FROM tableWHERE MATCH (col1, col2) AGAINST (‘expression’);
The MATCH function specifies the name of the columns, for which a particular search is carried out. The AGAINST function is used to specify the value of the query to be searched. One may specify search modifier in this function to carry out Boolean Mode search and also, to use Query Expansion.
Example:
Let us assume that the above table contains the following data:
| id | firstName | lastName | profile |
| 1 | MySQL | mysql | MySQL is an open source RDBMS. |
| 2 | PHP | Hypertext Preprocessor | PHP is an open source server side programming language. |
| 3 | JavaScript | js | JavaScript is a client side scripting using this we can validate form data & can create lots of dynamic contents on the web page. |
Run the following query to search the word ‘mysql’
-
-
SELECT *,MATCH(`firstName`,`profile`,`lastName`) AGAINST(‘mysql’) AS rel FROM `person` ORDER BY rel DESC
The output will be like this:
| id | firstName | lastName | profile | rel |
| 1 | MySQL | mysql | MySQL is an open source RDBMS. | 1.091023247894 |
| 2 | PHP | Hypertext Preprocessor | PHP is an open source server side programming language. | 0 |
| 3 | JavaScript | js | JavaScript is a client side scripting using this we can validate form data & can create lots of dynamic contents on the web page. | 0 |
Usage of Full-Text Function
Full Text function could be used at four main places:
- Natural Language Searches, where search is carried out for a particular string against the text collection specified as Match parameters.
- Boolean Mode Searches, using In Boolean Mode modifier and implied Boolean logic in the form of AND, OR and NOT.
- Stopwords Full Text to ignore the noise words like THE, HAVE, SOME and so on.
- Query Expansion Full Text search to automatically expand the range of words to be included in the search criterion. This means that once a search is carried out using a specific word, then the search is repeated and this time other words appearing significantly in the text collection are also included.
Some Points worth Mentioning
Here are few points, which are relevant to the usage of Full Text function:
- This function employ case-insensitive search criterion.
- Only MyISAM tables are incorporated in the search carried out by this function.
- The search is carried out amongst the words with default length of 4 characters or more.
- One may use ft_min_word_len and ft_max_word_len functions to specify the length of the search words.
- For large data sets, it is advised to employ Full Text function after loading the data into the table.
- Full Text search is incapable of working with some natural languages like Chinese and Japanese, which lack word delimiters.
Full Text search is good for large databases, where it is practically impossible to use LIKE and other MySQL methods.
Very helpful! Thankyou. Look forward to seeing more from you.