MYSQL FULLTEXT SEARCH - QUICK REFERENCE GUIDE - ADDING AND REMOVING OF FULL-TEXT INDEX
FULL-TEXT SEARCH TUTORIAL
Full-Text search refers to techniques for searching a single computer-stored document or a collection in a full-text database. Here, the search engine examines all of the words in every stored document as it tries to match search criteria.
Two Stages of Full-Text search
Basically there are 2 stages(1) INDEXING
(2) SEARCHING
With a small number of documents, it is possible for the full-text-search engine to directly scan the contents of the documents with each query, a strategy called "serial scanning."
If the number of documents to search is potentially large, or the quantity of search queries to perform is substantial, the problem of full-text search is often divided into two tasks: indexing and searching. The indexing stage will scan the text of all the documents and build a list of search terms (often called an index, but more correctly named a concordance). In the search stage, when performing a specific query, only the index is referenced, rather than the text of the original documents.
FULL TEXT Search is applicable to Fields having data type VARCHAR or TEXT
Full Text searching is only supported by the MyISAM storage engine.
VARCHAR and TEXT Columns that have been indexed with FULLTEXT can be used with special SQL statements that perform the full text search in MySQL
MATCH() ... AGAINST SYNTAX
Full-text searching is performed using MATCH() ... AGAINST syntax.Full-text Searches are not case sensitive
MATCH() takes a comma-separated list that names the columns to be Matched aganist the keyword
HOW TO ADD FULLTEXT INDEX TO A COLUMN
ALTER TABLE tbl_Products ADD FULLTEXT(Product_Title, Product_Details);
In the above SQL statement we are adding Fulltext indexes to 2 columns (ie, "Product_Title" and "Product_Details" ) of table "tbl_Products"
HOW TO USE FULLTEXT SEARCH
Once you have a FULLTEXT index, you can search it using MATCH and AGAINST statements
Here is the search Query:
FULLTEXT SEARCH BASED ON NATURAL LANGUAGE SEARCH MODE
SELECT Product_Title, Product_Details FROM tbl_Products
WHERE MATCH (Product_Title,Product_Details) AGAINST ('Kaspersky Antivirus');
the result of this query will be based on Relevance. For the above search query, The column list inside the MATCH function must exactly match that of the FULLTEXT index definition
FULLTEXT SEARCH BASED ON PARTIAL MATCH MODE
to obtain partial matches You can use boolean mode search like the one given belowSELECT Product_Title, Product_Details FROM tbl_Products
WHERE MATCH (Product_Title,Product_Details) AGAINST ('Kaspersky Antivirus' IN BOOLEAN MODE);
FULLTEXT SEARCH BASED ON EXPANDED RESULTS MODE
to Get more similar search results you can try "WITH QUERY EXPANSION"In most cases you would use this operation if the users query returned just a few results, you try it again WITH QUERY EXPANSION and it will add words that are commonly found with the words in the query.
SELECT Product_Title, Product_Details FROM tbl_Products
WHERE MATCH (Product_Title,Product_Details) AGAINST ('Kaspersky Antivirus' WITH QUERY EXPANSION);
DISPLAY HOW THE CREATE QUERY LOOKS LIKE
for this execute the following SQL statementSHOW CREATE TABLE tbl_Products;
It will show you the DDL for the table, including the system-assigned name for the index.
CREATE TABLE tbl_Products ( `Product_Id` int(11) NOT NULL auto_increment, `Product_Title` varchar(150) , `Product_Details` Text, PRIMARY KEY (`Product_Id`), FULLTEXT KEY `Product_Title` (`Product_Title`,`Product_Details`) ) ENGINE=MyISAM
Here in the above query , you can see that the full-text index is referred by the key name as `Product_Title`
HOW TO REMOVE FULLTEXT INDEX OF A COLUMN
To remove the Full-Text index You should refer the FULLTEXT KEY (from the above example it is "Product_Title" ) and the SQL statement comes like this:
ALTER TABLE `tbl_Products` DROP INDEX `Product_Title`;
OR
DROP INDEX `Product_Title` ON `tbl_Products`
FULL-TEXT SEARCHING IN MYSQL:
* Full Text searching is only supported by the MyISAM storage engine.* Searches are not case sensitive
* Short words are ignored, the default minimum length is 4 characters. You can change the min and max word length with the variables ft_min_word_len and ft_max_word_len
* Words called stopwords are ignored, you can specify your own stopwords, but default words include the, have, some - see default stopwords list.
* You can disable stopwords by setting the variable ft_stopword_file to an empty string.
* If a word is present in more than 50% of the rows it will have a weight of zero. This has advantages on large datasets, but can make testing difficult on small ones
DISADVANTAGES OF ADDING A FULLTEXT INDEX IN MYSQL
Well it will slow your inserts and updates little bit, but not really noticeable. After all it has to search through the content and index it. MySQL guys have done a good job tuning it for the optimal performance so it shouldn't need to be bothered that much. It only has a few choices for tuning anyways including word length etc.There are a few restrictions to this. You can read more about them at the reference links that I have given below.
Reference:
