Skip to main content

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


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:

Popular posts from this blog

How to apply for a new ration card and what are the documents required?

List of documents required for new ration card application in Kerala and how to apply


Application for a new ration card should be addressed to Taluk Supply Officer (TSO) / City Rationing Officer (CRO) of applicant's residing area.

Primary document required are Residence certificate, Income certificate and incase the applicant's name is in another ration card then he/she should submit the reduction certificate ontained from previous TSO / CRO as proof for removing his/her name from old Ration card.

You can get the new application from your currently residing Taluk Supply Office.

Payback Points - How to redeem - How to merge multiple payback accounts - Block Payback card - Payback customer care

Joomla and Forum Integration - Integrating Forums to Joomla

Joomla is one of the most popular CMS opensource packages. It is very easier to develop website's using Joomla. You just need to download Joomla package from Joomla's Official website www.joomla.org  and install it on your domain and later adding customizations to templates and feature and Your website is ready :). Now a days most websites provides a forum section for it users for discussing various article topics, gathering opinions etc.



Following are some best know forum opensource packages which can be integrated with Joomla and create a new forum experience for users


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 »
Member
Search This Blog