FULLTEXT Index Mysql (Using match — against to search in mysql column)

Search­ing is most gen­er­al fea­ture which every appli­ca­tion needs, We can imple­ment search by using like oper­a­tor of mysql but when we need very intel­li­gent search ‘like’ oper­a­tor cant help us (even after insert­ing wild­card char­ac­ters %) .  For that we have to use ‘match — against’ oper­a­tor of sql. It is spe­cial­ly for search­ing and will bring most appro­pri­ate result from your table. These can only be cre­at­ed with text-based columns like varchar,text,char.

Only MyISAM Stor­age Engine sup­ports FULLTEXT index­ing so you need to change your table‘s stor­age engine to MyISAM .

To cre­ate table with MyISAM Engine -

To Con­vert Exist­ing Table to MyISAM engine

Note: if you are run­ning lat­est ver­sion of mysql (>5.6) you dont need to change stor­age engine to myisam as inn­odb also sup­port FULLTEXT Index in mysql > 5.6

Now you have a table with FULLTEXT Index , so you can sim­ply query match-against like -

vivek

Web Developer & Server Admin, Skilled in Java , PHP , LAMP, Tomcat, Mongo DB & SQL. Available for freelancing project or Open Source Contribution, Feel free to contact me at contact@viveksoni.net .