Mysql FullText Tutorial

With the improved version mysql Mysql also got enriched with various smart tectiques.

One of the most beautiful technique is “MySql Fulltext” . This blog is totally dedicated to “Mysql Fulltext Feature”.

What is Mysql Fulltext feature

Apart from various other kind of index like “Uunique, Primary, Distinct”, Fulltext is also a  kind of Index which is only available with “MYISAM type tables of mysql”.  Also these can only be created with CHAR,VARCHAR and TEXT columns.

Why Mysql Fulltext required

Since its an unique kind of index so complete data of that column stored with proper keyword such that whenever any user search that particular keyword it match the maximum data from those fields which has fulltext index enabled.

Type of Fulltext Search

There are three types of  full text searches which is also known as search_modifier. These search_modifier is the third (and optional )argument of search query that is started with Match() AGAINST()

*. Boolean Search: This type of fulltext interprets the search string using the rule of special query language. Boolean Search contains the words that we are searching for from the database. With this kind of search type we can also send if this search word must be present or absent into that or it should weighted higher or lower than usual. Also there are some common words that are set up as “Stopwords” don’t get searched with that.
Example: SELECT * FROM articles WHERE MATCH (title,body)
AGAINST (‘+MySQL -YourSQL’ IN BOOLEAN MODE);

*. Natural Search: Natural Search type accepts search in natural language search. That means phrase are free of special operators. The stopword list also applies on this type of search text. words that are present in more than 50% of the rows are considered common and do not match. This is the default mode of fulltext search type.

Example: SELECT * FROM articles
     WHERE MATCH (title,body)
     AGAINST ('MySQL' IN NATURAL LANGUAGE MODE);

*. Query Expansion: Query expension search type is basically a modified version of Natural Search. In this type of search words from most relevant rows return by search are added to search string and then search done again.The query return rows from second search. We can specify this one by typing either IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION or WITH QUERY EXPANSION modifier specifies a query expansion search.

Example: SELECT * FROM articles
    WHERE MATCH (title,body)
    AGAINST ('database' WITH QUERY EXPANSION);

The following two tabs change content below.

• Bio
• Latest Posts

Chandra Shekhar

GCP Architect

Chandra Shekhar Pandey is Google certified Cloud engineer, I am Magento2 Trained developer. Having huge experience in designing cloud solution. I have around 12 years of experience with world enterprise IT companies and fortune 500 clients. During my architecture design I am always caring about high availability, fast performance and resilient system. From the programmer background I have huge experience in LAMP stack as well. Throughout my carrier I have worked on Retail, E-Learning, Video Conferencing and social media domain. The motive of creating cutehits was just to share the knowledge/solutions I get to know during my day to day life so that if possible I can help someone for same problems/solutions. CuteHits.com is a really a very effort for sharing knowledge to rest of the world. For any query/suggestion about same you can contact me on below details:- Email: shekharmca2005 at gmail.com Phone: +91-9560201363

Latest posts by Chandra Shekhar (see all)

• Best practices for micro service design - January 23, 2022
• Spring Boot - January 23, 2022
• Java - January 23, 2022