Full Text Search In MySQL ,a better way for MySQL Text search

One of the most common issue with MySQL is searching for a matching string from a huge database,suppose we need to search for ‘MySQL Tutorial’ it will only search the occurrence of ‘MySQL Tutorial’ neither ‘MySQL’ or ‘Tutorial’ also in MySQL compared to INT value search Char/Text is time consuming to solve this issue, we have Full Text Search .Full Text Search is a technique used by search engine to find the results in a database.we can use Full Text search in search Query.First you must create a full-text index on the table before you run full-text queries on a table.

Creating a full-text index inside a Table

ALTER TABLE TABLE NAME ADD FULLTEXT (FIELD1, FIELD2,FIELD3, etc)

Example

ALTER TABLE wptt_posts ADD FULLTEXT (`post_title`, `post_content`)

Now we created Full Text Index in wptt_posts table,next we will discuss how to use Full Text Search in MySQL

MySQL Query Using Full Text Search

SELECT * FROM TABLE NAME WHERE MATCH (FILED1FILED2,FILED3) AGAINST ('SEARCH WORD' IN NATURAL LANGUAGE MODE)

Example

SELECT * FROM wptt_posts WHERE MATCH (post_title,post_content) AGAINST ('Mysql Tutorials' IN NATURAL LANGUAGE MODE)

We can use the match case based on the Score

SELECT MATCH (post_title,post_content) AGAINST ('Mysql Tutorials') as score, post_title FROM wptt_posts WHERE MATCH (post_title,post_content) AGAINST ('Mysql Tutorials' IN NATURAL LANGUAGE MODE)





Where the score value represent the occurrence of search term in the filed

We can skip words (exclude words) from the result

For e.g “-Tutorials” means the search result should not contain Tutorials

SELECT * FROM wptt_posts WHERE MATCH (post_title,post_content) AGAINST ('+Mysql -Tutorials' IN BOOLEAN MODE)