Home  »     »     »  HOW TO USE FULLTEXT SEARCH IN MYSQL - MATCH ... AGAINST SYNTAX

HOW TO USE FULLTEXT SEARCH IN MYSQL - MATCH ... AGAINST SYNTAX

Tuesday, March 19, 2013

HOW TO USE FULLTEXT SEARCH in MYSQL - MATCH ... AGINST SYNTAX


First You have to apply FULL-TEXT index to the Columns of your table



Once you have a FULLTEXT index, you can search it using MATCH and AGAINST statements

Here is the search Query based on a sample table "tbl_Products":



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);


MYSQL FULL-TEXT SEARCH QUICK REFERENCE GUIDE



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:
HOW TO USE FULLTEXT SEARCH IN MYSQL - MATCH ... AGAINST SYNTAX.




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 »