Mysql Full-Text Search

The Full text Search will produce results faster than Simply using “OR” in sql query.

Mysql Full-Text Search Basics

For start using mysql full text search for multi-word queries we need to understand its basics.Firstly it can’t be implemented on Mysql innoDB storage engine tables,So the First thing we must do is convert our innoDB table to MyISam Engine,which is quite easy can be done using phpmyadmin under the operations tab .


Change Storage Engine to MyISAM
After we have our tables on MyISAM storage engine we just need to add full text index to our selected columns on the desired table.Which is again can be done using PHPmy admin or Use the Query below to add Full Text INDEX To our Tables.The code Below will add Full text index to  title and Description.columns
Add Full text Index to Table columns
ALTER TABLE inventory ADD FULLTEXT(title, description)
or using phpmyadmin as below.

 Mysql Full Text Index on Single Column:



adding full text Index on Single column using PHPmyadmin

Mysql Full Text Index on Multiple Column:



Add Full text INDEX On multiple Columns

Note:The FULL text index on single column and pair of column is differet things.

Usage MySql Full Text Search for Multiword String:

So Now Our table Are ready for Full text Search as we have provided full index.Now to search for any word say :we wana search “Sujal  Shah” then we’ll tyep Query as:
Searching using Full text Index.

SELECT *, MATCH(title, description) AGAINST ('Sujal Shah boy') AS artist
FROM artist_listing
ORDER BY artist DESC

The above query will match two columns title and description for Words “Sujal Shah boy” .For that to happen we need to have a multiple column Full text  index, which i just told to how to create above.

BOOLEAN MODE Operators in MySQL Full Text Search :

If We Use BOOLEAN FULL TEXT SEARCHE which means we’ll add boolean mode and we will have some useful operators available to us if we use Boolean mode.

As we just seen the Query “Sujal Shah boy” There is no Operator in between  .We could use operators for further refinement or as per needs, only if we use IN BOOLEAN MODE modifier. In Query “Sujal Shah boy” having no operator means Simply “OR” in boolean mode full text search.The Full text Search will Use OR  in boolean mode .which means Will find all the rows having any one of those words.

Example

SELECT *, MATCH(title, description) AGAINST ('+Sujal -Shah +boy' IN BOOLEAN MODE) AS artist
FROM artist_listing
ORDER BY artist DESC
+ means AND
- means NOT
[no operator] means OR
Here i have few examples using Mysql Full text search with operators:
  • +Sujal +Shah +boy:Will find only rows having all three words
  • +Sujal -Shah : will find rows that contain Sujal but not Shah
  • +Sujal Shah :will find rows that must contain Sujal, but rank rows higher if they also contain “Shah”.
  • +Sujal ~Shah :Fetch rows that must have the word “Sujal”, but if the row also have the word “Shah”, rate it lower than if row does not.
  • ‘+Sujal +(>Shah <boy)’ :Get the rows that contain the words “Sujal” and “Shah”, or “boy” and “boy” (in any order), but rank “Sujal Shah” high than “Sujal boy”.

 MySQL FULL TEXT SEARCH LIMITS :

  • Full text search can Only be used with MyIsam TABLES.
  • For Full text Search to to work, there must be Full text indexes defined otherwise SQL will throw error and Full text search will not work.

3 comments:

Unknown said...

PHP is gaining popularity day by day as a programming language. It is widely used for website development. If you want professional results, to drive in additional traffic to your site hire php developer is the best option...
http://www.webwingtechnologies.com/hire-php-developers.html

Unknown said...

We Create This Blog Learn Php at home is for beginner Programmers and Developers that should be learn web development using php step by step and can do examples of php with easy ways to Explain.

Tejuteju said...

Really nice blog post.provided a helpful information.I hope that you will post more updates like this Ruby on rails Online Course India