My Sql Full Text Search:

Hello Guys ! My Sql Full Text Search is Great Approach to speed up your projects when you have mysql has a back end.My sql Full text Search is standardized and Provide faster results.We might use other approaches as like we split out multi-word string using for or foreach loop then we build query and pass it on to back end and Outcome is much slower if we have huge database. We I think It is Best to use The Inbuilt and more standardized way which MySql has Provided us which is FULL TEXT SEARCH in MySQL . So I Suggest to use Full text search for multi-word search in MySQL .

Basics For Multiword mysql Full-Text Search

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 default 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 . See Image Below:

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 two columns Title and Description.

ALTER TABLE inventory ADD FULLTEXT(title, description)

or We can add from PHP my admin 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

Usage MySql Full Text Search for Multiword Search 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 “justin  bieber” then we’ll tyep Query as:

SELECT *, MATCH(title, description) AGAINST ('justin bieber baby') AS artist
FROM artist_listing
ORDER BY artist DESC

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

Operators in MySQL Full Text Search Using IN BOOLEAN MODE:

If We Use BOOLEAN FULL TEXT SEARCHES 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 “Justin bieber baby” 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 “Justin Bieber baby” 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.

SELECT *, MATCH(title, description) AGAINST ('+justin -bieber +baby' 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:

• +justin +bieber +baby:Will find only rows having all three words
• +jusitn  -bieber : will find rows that contain justin but not bieber
• +justin bieber :will find rows that must contain justin, but rank rows higher if they also contain “bieber”.
• +justin ~bieber :Fetch rows that must have the word “justin”, but if the row also have the word “bieber”, rate it lower than if row does not.
• ‘+justin +(>bieber <baby)’ :Get the rows that contain the words “justin” and “bieber”, or “baby” and “baby” (in any order), but rank “justin bieber” high than “justin baby”.

LIMITATIONS OF MySQL FULL TEXT SEARCH:

• mysql Full text search can Only be used with MyIsam TABLES.
• It is a Bit complex for a beginner.
• For Full text Search to work there must be Full text indexes defined in your table Either SQL will throw error and Full text search won’t work at all.

Conclusion:

MySql Full text Search is great functionally if we want to increase the search speed in our projects as rather than splitting up mutiple word string using loops and then Build sql then pass it on.The Full text Search will produce results faster than Simply using “OR”.I recommend Full text search for faster searches on multiple word queries .

I hope you enjoyed this post.Please Share if you liked it and find this helpful.Tnx for visiting designaeon. For now Adios!

About the author

Ramandeep Singh Is Btech Graduate in Computer Science.He has interest in Programming.He knows .net ,vb.net,c#.Ramandeep Singh is certified web developer in PHP and PHP Framework(cakePHP).Ramandeep Likes to write Tutorials on Blog,that is why he founded DesignAeon