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:

Strange problem occured while trying to create a CSV file using PHP Script - The file is not seen on FTP but can download using file's absolute path url

Strange problem occured while trying to create a CSV file - The file is not seen on FTP but can download using file's absolute path url Last day I came across a strange problem when I tried to create a csv file on therver using a PHP script. the script was simply writing a given content as a csv file. The file will be created runtime. What happened was, The script executed fine, file handler for new file was created and contents was wrote into the file using fwrite and it returned the number of bytes that was written.

Tecquila

Search This Blog