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”.