0

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 website Match either helpful, website, or both
+helpful +website Match both helpful and website
+helpful -website Match helpful but not website
+helpful ~website Match 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.

Save

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.