How to Search in MySQL using full-text indexing in PHP

Many developers make a big mistake during searching from MySQL use wildcard queries “LIKE %string%” which is a very slow query to search records so today we are going to learn full-text indexing search and its hundreds or thousands of times fast then wildcard wildcard queries.

How to Search in MySQL using full-text indexing in PHP


How to get it to work:

1. Create Table:

Insert Data:

2. Make sure that your database table uses MyISAM storage engine, if not then use below query to alter your table.

3. Create full-text index

4. Search it!

This will search records from database more faster then your wildcard queries and show you exact results you want for example:

You have data in your Table and you run below queries:

It will return only 1 record.

If you use full-text search it will return any row that matches “helpful” or matches “website”:

Boolean mode searching popular with internet search engines – allow you to proceed words with a + or a to force it to be present (+) or not present ().

This Boolean query search only helpful in the search not website as we add – sign before website.

Return only one record as our wildcard query we are forcing it to search complete string in database.

All of these examples are possible Boolean queries:

helpful websiteMatch either helpful, website, or both
+helpful +websiteMatch both helpful and website
+helpful -websiteMatch helpful but not website
+helpful ~websiteMatch helpful, but mark down as less relevant rows that contain website
+help*Match nice, helpful, helpfully, help website, etc
“helpful website”Match the exact term “nice website”
+helpful +(website blog)Match either “nice website” or “nice blog”
+helpful +(>website <blog)Match either “helpful website” or “helpful blog”, with rows matching “helpful website” being considered more relevant

So this is a simple and very useful tutorial I hope it helps you in your projects and make theme fast and more reliable in search.


Prakash S

Prakash S

I would like to introduce myself as a Software professional opting for the career in software industry. I'm Prakash S, a MCA graduate and trained as industry level practice for Software technology. Basically I am a PHP Developer but now days exploring more in HTML5, CSS, AngularJS and jQuery libraries.