April 15, 2015 3:43 pm

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

Hello friends after a long time today I am going to write a post on few of my readers request on How to quick 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.

Author Huzoor Bux

I am Huzoor Bux from Karachi (Pakistan). I have been working as a PHP Developer from last 5+ years, and its my passion to learn new things and implement them as a practice. Basically I am a PHP developer but now days exploring more in HTML5, CSS and jQuery libraries.

Tutorial Categories:
  • aswin

    It is showing an error like

    #1191 – Can’t find FULLTEXT index matching the column list

    • huzoorbux

      Did you altered your table for full index?

      CREATE FULLTEXT INDEX searchindex ON TableName(title);

  • Mariano

    When I create the TableName, it shows in mySQL as, tablename…!?…No capitals..

  • How can i delete full text index on some column?

  • Ayyaz

    You used wrong words in your table.
    I left column you are using word “helpful” but in right column you are using word “nice” for example “helpful website” left but in right column you used “nice website”. This might make reader confused so please fix it.

    Thanks for such useful article.

  • Hayat Hussain

    sir plz make totrial on how to make report in php plzzzz

  • kelvin barsana

    mine wouldn’t show result if i’m searching text with 4 characters below, how to configure that default?