Home   Best Sellers   Blogging   Coding & Design   Technology   SEO   Travel & living   Career   Videos   Tips   Calculators     
Home  »     »     »  MYSQL FULLTEXT SEARCH - QUICK REFERENCE GUIDE - ADDING AND REMOVING OF FULL-TEXT INDEX

MYSQL FULLTEXT SEARCH - QUICK REFERENCE GUIDE - ADDING AND REMOVING OF FULL-TEXT INDEX

Tuesday, March 19, 2013

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 below

SELECT 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 statement

SHOW 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:



How to link to this page?
If you wish to link to this page from your website, simply Copy and paste the above HTML code to your web page. It will appear on your page as:
MYSQL FULLTEXT SEARCH - QUICK REFERENCE GUIDE - ADDING AND REMOVING OF FULL-TEXT INDEX.




Share this!




comments powered by Disqus

This Weeks 7 Popular Posts


Subscribe to Recent Posts by Email
Stay connected to CROZOOM with regular Email notices of new Techie articles and IT Jobs. Updates will be delivered to your Inbox as soon as they are posted online.

Enter Your Email Address:  

Delivered by FeedBurner   RSS Feed

Search this Blog   



Urgent Openings for PHP trainees, Andriod / IOS developers and PHP developers in Kochi Trivandrum Calicut and Bangalore. Please Send Your updated resumes to recruit.vo@gmail.com   Read more »