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